The complex calculation (it used to have a bug in it but I helped FileMaker Inc find and fix it), does a kind of table lookup of data listed directly as part of the calculation. The + 1 adjustment to make the interval inclusive would seem exactly correct.
I don't think there would be any trouble getting the correct count if a user specifies a holiday date as one or both of the dates in your interval. The calculation should still compute the number of week days and then subtract out the number of holiday dates from that total.
BTW, i also know of an alternative approach: If you put all the dates of the calendar in a table and mark specific dates as "closed" whether because they are weekend dates or because it is a holiday, you can simply count all records on the interval that are not marked as "closed".
If I set the start date as a day before the holiday and end date as the holiday itself then my calculation outputs 1 day taken. Same thing if I set the end date later that week, the calculation remains correct as long as the start date is before the holiday itself.
If I set the start and end date as the holiday my calculation outputs 1 ... This is because the lookup subtracts the count by the same count number, it find an identical match for both days (Example New Year's day is 1, 1-1 = 0) there for that long calculation outputs 0 + 1 which I added at the end of the entire calculation and then - 0 from the holiday calculation ending in a result of 1 when it should be zero.
Last, if I set the start date as a holiday and end date as the following day, the result is 2. Again the look up finds an identical match in the start date, doesn't find an identical match in the end date and defaults to the next lowest result in a - 0 calculation.
The original work days calculation, with the added +1 to make the range inclusive works to calculate the correct number of work days, but there are better ways to structure the relationships to a Holidays table.
I'd have these fields in Holidays:
HolidayDate (date of holiday)
Then I'd use this relationship to count the number of holidays within your date range:
YourTable::StartDate < Holidays::HolidayDate AND
YourTable::EndDate > Holidays::HolidayDate
Then your calculation for working days from StartDate to EndDate would be:
5 * Int ( ( EndDate - StartDate ) / 7 ) +Middle ( "0012345501234544012343340123223401111234010012340" ; 7 * (DayOfWeek ( StartDate ) - 1 ) + DayOfWeek ( EndDate ) ; 1 ) + 1 - Count ( Holidays::HolidayDate )
You can also set up a layout with a calendar format to manage holiday dates. I've adapted the method used in this demo file for that purpose in one of my solutions: https://www.dropbox.com/s/e8d03xvwe8vtz85/Calendar.fp7
Very cool, thank you Phil.
I've implemented this and it works exactly as I want it to. I know I thanked you already but thanks again!
Note: I believe the original Knowledge Base article referenced here was created before inequalities could be used in a relationship.