3 Replies Latest reply on Aug 10, 2009 1:27 PM by philmodjunk

# Calc that will yield the number of workdays (Monday thru Friday) between 2 date fields

### Title

Calc that will yield the number of workdays (Monday thru Friday) between 2 date fields

### Post

Looks like I'm stuck in the calculation trench today.

I need a calc that will yield the number of work days (work days = Mon, Tues,Wed,Thurs,and Fri) between 2 date fields. The calc would need to work over consecutive months including the transition between the end of December and January of the following year. It would also need to work on leap years.

• ###### 1. Re: Calc that will yield the number of workdays (Monday thru Friday) between 2 date fields

Hi HappyHour,

you can try this formula:

Let ( [
a=startdate_field ; // may not be empty

b=enddate_field ;  // b ≥ a !!!!!!
day_a=DayOfWeek(a) ;
day_b=DayOfWeek(b) ;
date_a=a+Case( day_a=1 ; 1 ; day_a=7 ; 2 ; 0 ) ; // convert a date in the weekend to the date of the next monday
date_b=b+Case( day_b=1 ; -2 ; day_b=7 ; -1 ; 0 ) ; // convert a date in the weekend to the date of the previous friday
dow_a=DayOfWeek(date_a) ;
dow_b=DayOfWeek(date_b) ;

full_weeks=Floor((date_b-date_a)/7) ; // get the number of week between the 2 calculated dates

extra_days= dow_b-dow_a + Case ( dow_a > dow_b ;  5 ) ] ; // get the difference between the weekdays of the 2 calculated dates
5*full_weeks+extra_days+1 ) // add up both results to get the difference and add 1 to get the number of workingdays

regards, Menno

• ###### 2. Re: Calc that will yield the number of workdays (Monday thru Friday) between 2 date fields

HappyHour:

The solution by "menno" is correct (Thank you!).  You may also want to look at a few Knowledge Base Articles for some related date calculations with explanations:

Article #5281 - Calculating Number of Weekdays (Work Days) Between Dates

Article #5053 - Calculating a Weekday Date

Article #3926 - Calculate a Finish Date Given a Starting Date and the Number of Work Days