Calculating time using TimeStamp option excluding weekends
Posted:
Wed Jun 15, 2005 10:58 amby 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
Posted:
Thu Jun 16, 2005 5:34 amby John Sindelar
Hmm.
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.