I have a form where the applicant enters a start date and an end date. I would like to calculate the number of workdays (Monday-Friday) between the dates. Any ideas?
You want to use the NETWORKDAYS function, which also has optional parameters for holidays.
NETWORKDAYS(startDate, endDate, [holidays], [weekDays])
I had to calculate it for a table, but managed to get it working.
Thanks so much. You've been a great help.
Hi Jason,
I have same issue, but I would like to calculate the number of workdays (Saturday -Thursday) between the dates.
For example: If i have start date and end date then need to return total count of days without fridays between these dates.
Hi @████████,
To accomplish that, you can utilize the 4th "weekdays" parameter of the NETWORKDAYS function.
Note that that if you're not going to specify custom holidays, you'll still need the 3rd parameter to be empty for it to recognize there's a 4th parameter
=NETWORKDAYS(Start_Date,End_Date,,[0,0,0,0,0,1,0])
In the weekdays array, you set 1 for a non-weekday and 0 for a weekday. The values are listed in Sunday-Saturday order.
So in the example above, Saturday-Thursday are considered weekdays and Friday is the only day not counted in the calculation.
As-is, you might get an error when one date is selected and the other is empty, so to avoid that I'd wrap the whole thing in an IF function.
=IF(AND(Start_Date<>"",End_Date<>""),NETWORKDAYS(Start_Date,End_Date,,[0,0,0,0,0,1,0]),0)
The IF means it will return 0 unless both dates have values, in which case it will return the results of the calculation.
Hi @Jason,
I wanted to make the weekend (or non-working days) array editable. First, I used checkboxes, which returned True/False, but form didn't take it as 1/0 for the array. I ended up creating a text field to edit manually, and used semi-colon as the delimiter. The result is - with Monday and Tuesday non-working days in mind;
noWork ="0;1;1;0;0;0;0"
Then I used the SPLIT function inside the NETWORKDAYS to convert it to an array;
=NETWORKDAYS(dtStart,dtEnd,,SPLIT(noWork ,";"))
also tried with brackets;
=NETWORKDAYS(dtStart,dtEnd,,[SPLIT(noWork ,";")])
Per Formulas in Laserfiche Forms SPLIT function supposed to return an array showing as 0,1,1,0,0,0,0
But, both returns error "This field contains a calculation error. References: Start, End, noWork"
any thoughts?
Metehan,
You can do it with checkboxes. Checkbox variables return true/false in calculations which you can use in IF calculations to get a 1 or a 0.
For example,
Your array would be something like
[IF(Days_of_Week.Sunday,1,0),IF(Days_of_Week.Monday,1,0),IF(Days_of_Week.Tuesday,1,0),IF(Days_of_Week.Wednesday,1,0),IF(Days_of_Week.Thursday,1,0),IF(Days_of_Week.Friday,1,0),IF(Days_of_Week.Saturday,1,0)]
Making the calculation
=IF(AND(Start_Date<>"",End_Date<>""),NETWORKDAYS(Start_Date,End_Date,,[IF(Days_of_Week.Sunday,1,0),IF(Days_of_Week.Monday,1,0),IF(Days_of_Week.Tuesday,1,0),IF(Days_of_Week.Wednesday,1,0),IF(Days_of_Week.Thursday,1,0),IF(Days_of_Week.Friday,1,0),IF(Days_of_Week.Saturday,1,0)]),0)
And the end result
Worked great, thank you!
I was surprised that Split function didn't work.
My best guess is that SPLIT is returning an array of string values and the formula does not accept string values so it fails.
It is hard to tell because we don't really see the "types" in the display, but it is supported by the fact that simple addition doesn't work.
For example, this fails
=INDEX(SPLIT("1;2;3",";"),1) + 1
But this works because VALUE converts it to a number
=VALUE(INDEX(SPLIT("1;2;3",";"),1)) + 1
However, I'm not sure if there's a way to convert the entire array.