Elapsed seconds between timestamps (excluding weekends)
Does anyone know how to calculate the number of seconds between two timestamps excluding weekends?
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
Retrieving data ...