I'm trying to do a calculation for my Turnaround Times showing in days (number) minus the Saturdays and Sundays as these we don't work.
Will this custom function work for you?
xDayCountNoWeekends ( begin ; end ) =
Let ( [ x=1 //testing/*; begin = GetAsDate ( "1/27" ) // GetAsDate ( begin ) ; end = GetAsDate ( "1/31" ) // GetAsDate ( end ) */ ; begin = GetAsDate ( begin ) ; end = GetAsDate ( end ) ] ; Case ( begin >= end ; 0 ; Case ( DayOfWeek ( begin ) <> 1 and DayOfWeek ( begin ) <> 7 ; 1 ) + xDayCountNoWeekends ( begin + 1 ; end ) ) )
Let ( [
; begin = GetAsDate ( "1/27" ) // GetAsDate ( begin )
; end = GetAsDate ( "1/31" ) // GetAsDate ( end )
; begin = GetAsDate ( begin )
; end = GetAsDate ( end )
begin >= end
; Case (
DayOfWeek ( begin ) <> 1 and DayOfWeek ( begin ) <> 7
+ xDayCountNoWeekends ( begin + 1 ; end )
But what about holiday closures?
If you need to also account for those you'll need a different approach. In the past, I've set up a table with one record for each day of the calenda, setting a field to value to mark dates when the business is closed. I can then use a relationship to count the number of "open" records between two dated.
While the OP didn't ask about holidays, philmodjunk is right that you probably want to consider them.
I usually go the custom function route rather than a table of days, but it's certainly vaild. Just make sure your system will also automatically add day records as needed. I've dealt with a number of support calls from people with db's that used this method, then lost touch with their "hands-on" developer, and at a certain point when they get beyond the day records everything goes haywire. I'm sure Phil would account for this, but be sure you do, too, if you use this method. If those day records don't get created automatically, you'd be assuming that either a) you're going to be there to continue adding records yourself indefinitely or b) you're not going to use the system long enough to run out of days.
If you want to explore custom functions for this, here's one that takes into account holidays: FileMaker Custom Function:CalcDaysBetween ( startDate ; endDate ; numDays ; $$HolidayList ; countWeekends ; returnNeg ) There's others like it on the same site, but this one looks promising.
I figure that since you need to manage the dates for holidays, just go the whole way and set up a calendar layout where each square in the calendar creates/modifies a record in the table of holiday dates. That makes it easy for the users to periodically sit down and add holiday closures to the calendar. They can also decide to be open and work on a date on which they would normally be closed if the need arises.
Retrieving data ...