would you please check my latest xlsform?
@ahaiba, would you mind sharing your xlsform with the community instead of sharing the screenshot? Maybe the community could add to your xlsform and carry it forward.
form.xlsx (10.4 KB)
This is the xlsform. please tell me where is the bug in calculating business days. (For example 20 August 2021 to 02 September 2021) it prints out 11 days (it should be 5 business days).
1 Like
My error, there’s a bug in my algorithm as it wont always pickup whether there is a weekend between 2 dates; eg Thur thru following Wed is <7 days (so weeks=0), but there is still a weekend between them. So it could be a week out. I’ll fiddle around with it this weekend and see what I can come up with…
BTW, you may want to be precise as to what you define as ‘between’ (specifically, inclusiveness)? eg is start=Mon and end=Fri count as 4 business days, or 5?
1 Like
counted as 4 business days (in my work, we don’t count the day of the start date (whether it is a weekend or not).
Thanks, would you please update my xlsform if possible when you find a suitable solution?
so start=Sun and end=Fri would be 5 days? (that’ll affect the algorithm…)
1 Like
@Xiphware yes, for this it would be 5 days. The whole algorithm can be done one single Excel function (NETWORKDAYS).
If you find the bugs, please update my xlsform I attached, thank you!
The whole algorithm can be done one single Excel function (NETWORKDAYS).
Try this (adapted from microsoft access - Equivalent of Excel's NETWORKDAYS function with Jet ADO - Super User):
networkdays.xlsx (10.0 KB)
Please note, Excel’s NETWORKDAYS() function is inclusive (so Mon thru Fri = 5 days). From https://exceljet.net/excel-functions/excel-networkdays-function:
…NETWORKDAYS includes both the start date and end date when calculating workdays. If you give NETWORKDAYS the same date for start date and end date, and the date is not a weekend or holiday, it will return 1.
1 Like
@Xiphware thanks i will rest this later and let you know.
i know, in Excel we just add minus one to NETWORKDAYS.
1 Like
hi, sorry I have tried your solution and it is still not working. I have uploaded it to Kobo on a seperaate project but it is not working and the file has errors
Hi @ahaiba
Have you tried following the logic provided by @Xiphware to troubleshoot if there is an issue with the syntax?
Regards,
Stephane
investigating… Try running the form on ODK - XLSForm Online v2.x in the mean time. Not sure yet why Kobo preview is throwing a weird error [Kobo and XLSForm Online are both using same Enketo to preview forms…]
1 Like
@stephanealoo I have tried @Xiphware xlsform and it shows error loading in KoboToolbox, but on ODK it is working!
@Xiphware yes, it is working on ODK thank so much, but I am not sure why it is not working on KoboToolbox!
Yup. So it looks like the issue is around Enketo’s handling of the jr:choice-name() function, which appears to have changed slightly between the version Kobo is running and the one XLSForm Online is using (which I suspect is more recent…).
Basically, to validate the form Enketo is running it with the initial defaults, and seeing what if any errors are thrown. In this case, it tried running
jr:choice-name(format-date(${start},"%a"), '${lookuptable}')
before the ${start} date had been set (!), which therefore produced an error, which was interpreted as the entire form definition being bad (although technically its not). Whereas, apparently in later versions of Enketo, this validation behavior - at least around the jr:choice-name() function - is a bit different [perhaps this: hack around jr:choice-name · Issue #10 · enketo/enketo-validate · GitHub ?]
The workaround/fix was to assign start and end a default date, to make the initial validation pass happy. Try this form instead; I tested it under both Kobo preview and XLSForm Online and seems to work OK:
networkdays.xlsx (10.8 KB)
[@martijnr Does this sound about right?]
1 Like
I think KoBo doesn’t use enketo-validate, so there may have been an issue in enketo-core with jr:choice-name.
Indeed, the xlsform.org preview server is normally the most recent, so it was likely fixed. Yay.
2 Likes
Hello @Xiphware, I hope you are well, I have observed all your help in calculating the number of week of the year of a date and I have tried desperately to do it but I have not been able to, could you help me if you have the updated file in xlsform format called weekyear.xls? I have run it several times and it works for me up to the day number of the year. Attached image. Thank you very much if you can help me.
I solved it, the problem was that it generated the day of the week in Spanish and in the table “daynames” are in English for this reason it presented the failure sent.
2 Likes
