Calculate Business Days Formula Issue

[former_member406941]

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;

[Former Member]

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

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

Ian

[abhilash_kumar]

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