Skip to Content

### Calculate Business Days Formula Issue

Hello!

My formula isn't functioning correctly.....I can't figure out why.

Example: FirstDay = Friday 1/20/17. LastDay = Monday 1/23/17. EndDate-StartDate correctly calculates 1 day.

Example: FirstDay = Friday 1/20/17. LastDay = Monday 1/30/17. EndDate-StartDate should equal 6 days, my formula calculates 8 days (obviously it's not subtracting 2 of the 4 weekend days)

Example: FirstDay = Friday 1/20/17. LastDay = Monday 2/6/17. EndDate-StartDate should equal 11 days, my formula calculates 13 days (obviously it's not subtracting 2 of the 6 weekend days)

Here is formula (that isn't functioning correctly) to calculate FirstDay, LastDay and Weekends.

Thank you in advance for your help!! - Rich

If DayOfWeek(FirstDay) = 7 Then

StartDate := FirstDay + 2

Else

If DayOfWeek(FirstDay) = 1 Then

StartDate := FirstDay + 1

Else StartDate:=FirstDay;

If DayOfWeek(LastDay) = 7 Then

EndDate := LastDay + 2

Else If DayOfWeek(LastDay) = 1 Then

EndDate := LastDay + 1

Else

EndDate := LastDay;

Days:= (EndDate - StartDate);

if Days >= 7 then

WeekEnds := (Round((Days/7),0))*2

else if DayOfWeek(StartDate) > DayOfWeek(EndDate) then

WeekEnds := 2 else WeekEnds := 0;

##### Add comment
10|10000 characters needed characters exceeded

### 2 Answers

• Feb 06, 2017 at 07:46 AM

Hi Rich,

Here's an easier formula:

```Datevar FirstDay := cdate(2017,01,20);
Datevar LastDay := cdate(2017,02,06);
DateDiff ("d", FirstDay, LastDay) - (DateDiff ("ww", FirstDay, LastDay, crSaturday)) - (DateDiff ("ww", FirstDay, LastDay, crSunday))```

-Abhilash

##### Add comment
10|10000 characters needed characters exceeded
• Feb 06, 2017 at 10:55 AM

If you want to exclude public holidays too this formula has always worked for me.

http://www.kenhamady.com/formulas/form01.shtml

Ian

##### Add comment
10|10000 characters needed characters exceeded