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

  • Get RSS Feed

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