Only weekends? No three day weekends due to holidays' etc?
There are calculations for the number of days between dates that could be adapted to subtract out 48 hour's worth of seconds for every weekend, once can be found in the KnowledgeBase, but the expression assumes that every weekend and only weekends be omitted and this often does not match the real world. There are other approaches that work from a calendar of weekends and holidays that can flexibly omit not only weekends but any holiday dates that also fall on the time interval in question.
I am interested in both approaches. The latter would be ideal.
They are very different approaches.
Here's the knowledge base article on counting week days: http://help.filemaker.com/app/answers/detail/a_id/5281/kw/work%20day
I'm leaving it up to you to convert days into seconds, but this should be possible.
Accounting for holidays requires a table of either holiday dates, dates you are not on holiday or both with a status field to identify which are which. Then you can deduct 24 hours worth of seconds for each holiday that falls in the interval between your two time stamps.
with both approaches, you may need to use GetAsDate ( TimeStampfield ) to extract just the dates in question in order to allow for holidays and week ends.
Thanks. I think that's enough to get me started.
Here's what I ended up doing. Since I wanted to store the result anyway, I used a script instead of calculation. I found that I needed to adjust the formula from the knowledge base by subtracting 1. If the end date was immediately succeeding the start date, the number of days between them needed to be zero instead of 1 since I was already accounting for that time by counting seconds included in the first and last days. The script stores the result in Table1::TotalSeconds.
Before running the script, I just need to set 2 variables: $$StartTimestamp and $$EndTimestamp
- Allow User Abort [ Off ]
- Set Error Capture [ On ]
- # If started and finished on the same day, just subtract
- If [ GetAsDate($$StartTimestamp) = GetAsDate($$EndTimestamp) ]
- Set Field [ Table1::TotalSeconds; GetAsNumber(GetAsTimestamp($$EndTimestamp) - GetAsTimestamp($$StartTimestamp)) ]
- Exit Script [ ]
- End If
- # Count the seconds included in the last day
- Set Variable [ $LastDaySeconds; Value:GetAsNumber( GetAsTimestamp($$EndTimestamp) - Timestamp(GetAsDate($$EndTimestamp) ; Time(0;0;0) ) ) ]
- # Count the seconds included in the first day
- Set Variable [ $FirstDaySeconds; Value:GetAsNumber( Timestamp(GetAsDate($$StartTimestamp) ; Time(23;59;59)) - GetAsTimestamp($$StartTimestamp) ) ]
- # Count the number of days between dates, excluding weekends
- Set Variable [ $WeekdaysBetween; Value:5 * Int ( (GetAsDate($$EndTimestamp) - GetAsDate($$StartTimestamp) ) / 7 ) +Middle ( "0012345501234544012343340123223401111234010012340" ; 7 * (DayOfWeek ( GetAsDate($$StartTimestamp) ) - 1 ) + DayOfWeek ( GetAsDate($$EndTimestamp) ) ; 1 ) - 1 ]
- # Count the number of holidays between dates
- Go to Layout [ “Holidays” (Holidays) ]
- Enter Find Mode [ ]
- Set Field [ Holidays::Date; GetAsDate($$StartTimestamp) & "..." & GetAsDate($$EndTimestamp) ]
- Perform Find [ ]
- If [ Get(LastError) = 401 ]
- Set Variable [ $Holidays; Value:0 ]
- Set Variable [ $Holidays; Value:Get(FoundCount) ]
- End If
- Go to Layout [ original layout ]
- # Convert number of days between dates to seconds
- Set Variable [ $WeekdaySeconds; Value:$WeekdaysBetween * 86400 ]
- # Convert holidays to seconds
- Set Variable [ $HolidaySeconds; Value:$Holidays * 86400 ]
- # Calculate total seconds
- Set Field [ Table1::TotalSeconds; // Seconds included on First Day + Seconds included on Last Day + seconds of all days between - Holiday Seconds $FirstDaySeconds + $LastDaySeconds + $WeekdaySeconds - $HolidaySeconds ]