[Return to contents]
Last updated: before December, 1998
The following formula will return the number of weekdays between the dates contained in DB.STARTDATE and DB.ENDDATE:
If(Year(DB.STARTDATE) <> Year(DB.ENDDATE),DB.ENDDATE - DB.STARTDATE - (WeekOfYear(DB.ENDDATE) + (Year(DB.ENDDATE) - Year(DB.STARTDATE) - Year(DB.STARTDATE)) * 52 - WeekofYear(DB.STARTDATE)) * 2, DB.ENDDATE-DB.STARTDATE - (WeekofYear(DB.ENDDATE) - WeekofYear(DB.STARTDATE)) *2)
Another method use two formula:
TotalDaysDiff - (((TotalDaysDiff + DayOfWeek(StartDate) - DayOfWeek(EndDate) + 7) * 2 / 7) - If(DayOfWeek(StartDate) = 1, 0, 1) - If(DayOfWeek(EndDate) = 1, 2, 1))
You may get funny results from these formulae if either the start or end date is a Saturday. You can get around this problem by not working on weekends!
You can have the formula automatically subtract any public holidays, if any, by adding the following formula segment for each possible public holiday:
-If(START <= '25/12/96' and FINISH >= '25/12/96', 1, 0)
where 25/12/96 is a public holiday. Make sure you use the same date format as your database.