Page 1 of 1

Calculating time using TimeStamp option excluding weekends

PostPosted: Wed Jun 15, 2005 10:58 am
by Roger - Schmidt
We have a FileMaker database which Management wants to track by Time how long work is in each Department excluding weekends.
Most Department are 24 hours and no weekends work.

Example of fields:

Files Here TimeStamp
Done PF TimeStamp

Time in PF Calculation - Output Time

PostPosted: Thu Jun 16, 2005 5:34 am
by John Sindelar

Well, the following will return the number of Days (not hours) between two dates, excluding weekends:

Int((EndDate - StartDate)/7) * 5 + If(DayofWeek(EndDate) < DayofWeek(StartDate),
Min(5, DayofWeek(EndDate) - 1) + Max(0, 6-DayofWeek(StartDate)),
If(DayofWeek(StartDate) < 7, Min(6,DayofWeek(EndDate)) -
DayofWeek(StartDate), 0)) + 1

Taking that, and knowing the your departments never start or end work on weekends, we can say that all weekend days occurring between your StartDate and EndDate will debit 24 hours from the time worked. So, subtract the value returned from the calc above (work days) from the straight duration in days (total days) to get the number of weekend days in your duration. Multiply the number of weekend days by 24 hours and subtract that from the raw duration in hours.