This happens occasionally - found some further answers which led to a solution.
On a single row, you'll have a start and end date and the following formula to calculate days:
5 * Int ( ( EndDate - StartDate ) / 7 ) +Middle ( "0012345501234544012343340123223401211234010012340" ; 7 * (DayOfWeek ( StartDate ) - 1 ) + DayOfWeek ( EndDate ) ; 1 )
I got this from the FM knowledgebase here:
I must say that for July 2014, it's forgotten a day and has returned 22 instead of 23. Seems to be consistently wrong where there are 31 days in a month.
Any idea why that may be?
Try this calculation:
D = YourDateField ;
startDate = Date ( Month ( D ) ; 1 ; Year ( D ) ) ;
endDate = Date ( Month ( D ) + 1 ; 0 ; Year ( D ) )
5 * Int ( ( EndDate - StartDate ) / 7 ) +Middle ( "0012345501234544012343340123223401211234010012340" ; 1 + 7 * (DayOfWeek ( StartDate ) - 1 ) + DayOfWeek ( EndDate ) ; 1 )
Thanks for this - it's working good for me now with your amended formula. I don't pretend to fully understand what's going on with this particular formula (which is something I like to know), but I'm happy to take this!
The calculation builds a table of values quoted string and then looks up a value from it. (I recognized it as I used to use that method to store a 3 dimensional array in a version of (not visual) BASIC that didn't support arrays of strings.
Often the number of work days in a time interval has to subtract out any holidays that fall on that time interval. If such is the case, I find it simpler to use a table of holidays and adapt it to include weekend closures in the same table. Then it's simply a matter of using a relationship or ExecuteSQL to count related records that fall on a date range.