Minor quibble: These are simply calculations, not custom functions.
There's a typo in the calculation. If you fix that typo, it should work for you:
5 * Int ( ( EndDate - StartDate ) / 7 ) +Middle ( "0012345501234544012343340123223491111234010012340" ; 7 * (DayOfWeek ( StartDate ) - 1 ) + DayOfWeek ( EndDate ) ; 1 )
5 * Int ( ( EndDate - StartDate ) / 7 ) +Middle ( "0012345501234544012343340123223401111234010012340" ; 7 * (DayOfWeek ( StartDate ) - 1 ) + DayOfWeek ( EndDate ) ; 1 )
Thank you for your post.
There is definitely a typo in the calculation, and a big thanks to PhilModJunk for pointing this out and correcting it. The article has been updated.
This calculation is very useful. It would be nice if the text makes it more clear that StartDate is included and EndDate is not included in the number of workdays. I would prefer to include both StartDate and Enddate, but that is easy to correct.
Unfortunately, the additional calculation for the holidays is not compatible as it includes holidays on EndDate but not on StartDate. That is a bit more difficult to fix as a Lookup is used. One should make Lookup Low check the day before StartDate if holidays on StartDate are to be included, and Lookup High should lookup the day before EndDate if EndDate is to be excluded. Or add a calculated field to the Holiday table with a date that is shifted 1 day up (later) from the original holiday date, and use that field for the Lookup. Somebody with more experience than I, please check this suggestion.
Finally, please add to the text a warning that holidays that occur on Saturday or Sunday should not be added to the table.
Thank you for your post.
The article has been updated. Just below the HOLIDAYS table, the following has been added:
"NOTE: Only include those holidays that occur during a work day; not weekends."
To StartDate and EndDate are to both be included, then just add one more day to the result.
To include EndDate too isn't as easy as adding one more day to the result. The EndDate could be in a weekend (or a holiday). For calculating the workdays, you have to add one day to both occurrences of EndDate in the calculation. Not too difficult, but it would be nice to explain in the text that EndDate itself doesn't count.
The real problem is that for finding holidays, StartDate is excluded and EndDate is included. This is wrong. For Monday December 22, 2014 till Thursday December 25, 2014 the result will be 2 working days as Christmas on EndDate gets subtracted from the result. And Thursday January 1, 2015 till Saturday January 3, 2015 will return 2 working days as New Year on StartDate is ignored. That is, if I understand the algorithm correctly. I quoted the explanation from the article with a changed StartDate.
"These two fields determine the number of holidays that have occurred between StartDate and EndDate. For example, StartDate = 7/5/07 and EndDate = 7/15/07. 7/5/07 looks up into HOLIDAYS, and a match is found, and 5 is returned from Counter. 7/15/07 looks up into HOLIDAYS 2, and no match is found. However, the next lower value is 7/5/07 (Independence Day Observed), and 5 is returned from Counter. Subtracting Lookup Low from Lookup High (5-5=0) does not return the number of Holidays that have occurred between 7/5/07 and 7/15/07."
To count records in Holidays, use this relationship to the Holidays table:
YourTable::StartDate < Holidays::Date AND
YourTable::EndDate > Holidays::Date
Or you can use a script or custom function to fill a text field with a list of all dates from StartDate to EndDate and use this relationship:
YourTable::DateList = Holidays::Date
PS. This thread does not automatically appear in recent items when someone posts a comment to it. Thus it is easy to miss unless you happen to be subscribed to it and get an emailed copy of the comment. To make sure that you get maximum visibility for your comment, you might want to start a new thread and post a link back to this thread so that your comments pop up in Recent Items.
Thank you, PhilModJunk. Nice solution. I changed the relationship to the Holidays tabel as you suggested, and then created
NumberOfHolidays Calculation Unstored, from YourTable, = Count ( Holidays::Date )
Unfortunately, this gave inconsistent results. It might have something to do with caching old results, that I don't really understand. Changing it to
... Unstored, from YourTable, = ExecuteSQL ( "select count (*) from Holidays where Date between ? and ?" ; "" ; "" ; StartDate ; EndDate )worked.P.S. As long as this discussion pops up in Google and the original article gets updated, everybody should be fine.
Ouch. There is still a typo in the article's string "0012345501234544012343340123223401111234010012340".
It should read "0012345501234544012343340123223401211234010012340".
Thank you for pointing this out. The article has been updated again, where both references to the array string have been updated.
Might it be more accurate to say:
"To have the StartDate and EndDate both included, add 1 to the Endate"
Instead of adding 1 to the final result?
Would this formula work?
5 * Int ( ( (EndDate + 1) - StartDate ) / 7 ) +Middle ( "0012345501234544012343340123223401211234010012340" ; 7 * (DayOfWeek ( StartDate ) - 1 ) + DayOfWeek ( EndDate + 1 ) ; 1 )