Calculating The Number Of Work Days (weekdays) Between Two Date Fields (DB)

This information was provided by Claris Corporation on 16 March 1998, and incorporated into Apple Computer's Tech Info Library.
To calculate the number of work days (weekdays) between two fields formatted as dates will require the following formula. This tech note assumes that your database contains the date fields, StartDate and EndDate. Define a new calculation field and name it, TotalWorkDays. The formula for this calculation is as follows:

TotalWorkDays (calculation, number result)
=INT (('EndDate'-'StartDate')/7)*5 + MOD ('EndDate'-'StartDate',7) - IF (MOD ('EndDate' - DATE
(1904,1,3),7) < MOD ('StartDate'-DATE (1904,1,3),7),2,0)

Here's the logic:

1. Take the number of weeks between the two dates and multiply the result by
five working days per week.

2. Add the remaining days which do not make up a full week.

3. If EndDate occurs on a day of the week before StartDate, then subtract the 2
weekend dates.

This formula will provide an accurate calculation of the work days between any two dates. It assumes that StartDate is earlier than (or equal to) EndDate. This does not count the first and last days as work days. If you want Monday through Tuesday to return 2 work days (instead of 1), add one (+1) to the end of the calculation formula.


This formula does not account for Holidays.

Published Date: Feb 18, 2012