Calculating time using TimeStamp option excluding weekends

General Information about upcoming products, upgrades, etc.
PostPosted: Wed Jun 15, 2005 10:58 am
:(
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
SeedCode Staff
SeedCode Staff
Posts: 2759
Joined: Thu Nov 20, 2003 11:01 am
PostPosted: Thu Jun 16, 2005 5:34 am
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.
John Sindelar
SeedCode

Return to FileMaker Products (General)

Who is online

Users browsing this forum: No registered users and 0 guests

(855) SEEDCODE
support@seedcode.com
Follow us: