Calculate business days between 2 dates

User Support Form Building
,
1

Hello,

I am trying to calculate the business days between 2 date questions in xlsform, but I can’t find a formula similar to NETWORKDAYS in Excel.

I also couldn’t find such a a thing in Kobo Form Builder.

I would appreciate it very much if someone can share a solution for this.

Best,
Abdul

2

Welcome to the community, @ahaiba! You could do it as outlined in the post discussed previously:

3

Thanks @Kal_Lam, but this calculates the days, not “business days” as the NETWORKDAYS function do.

4

You mean, you wish to exclude Saturday and Sunday in your calculation?

5

Yes exactly, excluding the weekends.

6

To exclude weekends you cannot just subtract 2 for every week between the two dates, since it’ll also depend on whether the first/last dates lie on a weekend. So you’ll need to determine whether the first and/or last day are Saturday/Sunday. Have a look at this posting; although it is about weeks, it describes the tools necessary:

In particular, format-date(${date},"%a") will give you the day-of-week for a date (from which you can determine the needed Sat/Sun…)

Good luck. Please post your solution here when you’ve solved it. :wink:

1 Like
7

Hello, thanks but I have tried to use your solution and I couldn’t get the desired output. would you please share your code?

8

@Xiphware

I have did this calculation (to calculate business days between two dates excluding the start day):

if(format-date(${START_DATE},“%a”) = “Sat” or format-date(${END_DATE},“%a”) = “Sun”, ${END_DATE} - ${START_DATE} - 2, if(format-date(${START_DATE},“%a”) = “Sun” or format-date(${END_DATE},“%a”) = “Sat”, ${END_DATE} - ${START_DATE} - 1, ${END_DATE} - ${START_DATE}))

It works but not for all cases. For example if the weekend(s) exists between the start and end dates, then the above code doesn’t work. Would you please check?

@Kal_Lam please also have a look on this code.

9

Psuedo code (ie you still need to fully translate this algorithm to XLSForm…):

day1 = int(decimal-date-time(${date1})
day2 = int(decimal-date-time(${date2})

// adjust start & end dates if they fall on a weekend

if format-date(${date1},"%a") = “Sat” then day1 += 2
else if format-date(${date1},"%a") = “Sun” then day1 += 1

if format-date(${date2},"%a") = “Sat” then day1 -= 1
else if format-date(${date2},"%a") = “Sun” then day1 -= 2

// determine number of weekends

delta = ${day2} - ${day1}
weeks = int(${delta} div 7)
 
// remove weekends

delta_business = ${delta} - (${weeks} * 2)

There are some edge cases - eg start on Saturday and end the next day (Sunday), but presumably these are not permitted…

1 Like
10

image
image1627×199 10.4 KB

@Xiphware Thanks for writing the algorithm. I have translated it into the xlsform code shown in the image, but sometimes it doesn’t work, IS there any bug in my xlsform translation? (For example 20 August 2021 to 02 September 2021) it prints out 11 days (it should be 5 business days).

11

Thanks so much, @Xiphware for sharing the solution.

Hi @ahaiba
Could you send your XLS form for review? You seem not to have used rows 4 and 5 within your subsequent calculations.

Stephane

2 Likes
12

image
image1751×196 10.2 KB

@stephanealoo Thanks for your reply. I have used lines 4 and 5, please check becuase I still don;t get the right answer.

13

@Xiphware @stephanealoo

would you please check my latest xlsform?

14

@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.

15

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).

FYI @Xiphware @stephanealoo @Kal_Lam

1 Like
16

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
17

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?

18

so start=Sun and end=Fri would be 5 days? (that’ll affect the algorithm…)

1 Like
19

@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!

20

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