Workdays (inc Sat) formula

[Guest]

I need a formula to tell me the number of workdays between two dates, the
problem being that Saturday I consider a workday.

The NETWORKDAYS function obviously won't do.

Thanks
Michael

 

[Frank Kabel]

Hi
if A1 stores the start date and B1 the end date try the following array
formula (entered with CTRL+SHIFT+ENTER):
=B1-A1+1-SUM(IF(WEEKDAY(A1-1+ROW(INDIRECT("1:"&TRUNC(B1-A1)+1)))=1,1,0)
)

 

[Bob Phillips]

Michael,

Here is a solution posted by Myrna Larson

If you need to include holidays, as you can do with NETWORKDAYS,

=NETWORKDAYS(A1+1,A2+1,HolidayRange)

This just "tricks" Excel into thinking that a Friday is really a Saturday, a
Saturday is a
Sunday, and a Sunday is a Monday, so it excludes Fridays and Saturdays from
the count, but not
Sundays.


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Frank Kabel]

Hi Bob
maybe it's just too late for me but wouldn't that lead to a wrong
result if you want to count the number of Mondays-Saturdays?

 

[Bob Phillips]

Hi Frank,

Yes, on re-reading I got that slant, If you want to include holiday lists
aka NETWORKDAYS I think this need VBA. Here is a routine I use

'---------------------------------------------------------------------
Function DaysBetween(StartDate, _
EndDate, _
Optional Holidays, _
Optional IncSat As Boolean = False, _
Optional IncSun As Boolean = False)
'---------------------------------------------------------------------
Dim cDays As Long
Dim StartDateWe As Date
Dim EndDateWE As Date

'check if valid arguments
If (Not IsDate(StartDate)) Then
GoTo DB_errValue_exit
ElseIf (Not IsDate(CDate(EndDate))) Then
GoTo DB_errValue_exit
ElseIf (StartDate > EndDate) Then
GoTo DB_errValue_exit
ElseIf (Not IsMissing(Holidays)) Then
If (TypeName(Holidays) <> "Range" And _
TypeName(Holidays) <> "String()" And _
TypeName(Holidays) <> "Variant()") Then
GoTo DB_errValue_exit
End If
End If

#If fDebug Then
Debug.Print StartDate & ", " & _
EndDate & ", " & _
IncSat & ", " & _
IncSun
#End If

cDays = EndDate - StartDate + 1
'determine the saturday after end date
EndDateWE = EndDate + (7 - Weekday(EndDate, vbSunday))

'reduce by appropriate no of saturdays
If Not IncSat Then
cDays = cDays - ((EndDateWE - StartDate) \ 7)
End If
'reduce by appropriate no of sundays
If Not IncSun Then
cDays = cDays - ((EndDateWE - StartDate) \ 7)
End If

'reduce by 1 if enddate is a saturday and saturdays not included
If Weekday(EndDate, vbSunday) = vbSaturday And Not IncSat Then
cDays = cDays - 1
End If
'reduce by 1 if startdate is a sunday and sundays not included
If Weekday(StartDate, vbSunday) = vbSunday And Not IncSun Then
cDays = cDays - 1
End If

'reduce by any holidays
If (Not IsMissing(Holidays)) Then
cDays = cDays - NumHolidays(StartDate, EndDate, Holidays, IncSat,
IncSun)
End If

DaysBetween = cDays

Exit Function

DB_errValue_exit:
DaysBetween = CVErr(xlErrValue)

End Function

'---------------------------------------------------------------------
Function NumHolidays(ByVal StartDate, _
ByVal EndDate, _
ByVal Holidays, _
ByVal IncSat As Boolean, _
ByVal IncSun As Boolean)
'---------------------------------------------------------------------
Dim cHolidays As Long
Dim cell

For Each cell In Holidays
If (IsDate(cell.Value)) Then
If (CDate(cell) >= StartDate And CDate(cell) <= EndDate) Then
cHolidays = cHolidays + 1
If (Weekday(CDate(cell), vbSunday) = vbSaturday) Then
If Not IncSat Then
cHolidays = cHolidays - 1
End If
ElseIf (Weekday(CDate(cell), vbSunday) = vbSunday) Then
If Not IncSun Then
cHolidays = cHolidays - 1
End If
End If
End If
End If
Next cell

NumHolidays = cHolidays

End Function

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Daniel.M]

Hi,

With your dates in A1 and A2,

If you don't have holidays to consider:

=SUM(INT((A2-WEEKDAY(A2-{1;2;3;4;5;6})-A1+8)/7))

If you do have holidays (and want to exclude them):

=SUM(INT((A2-WEEKDAY(A2-{1;2;3;4;5;6})-A1+8)/7))-SUMPRODUCT(
ISNUMBER(MATCH(ROW(INDIRECT(A1&":"&A2)),Holidays,0))*(WEEKDAY(
ROW(INDIRECT(A1&":"&A2)))<>1))

Regards,

Daniel M.

 

[Norman Harker]

Hi Frank, Bob, and "x"

I think that the following non-array formula works and allows removal of
holidays as well:

=B1-A1-(INT((B1-(IF(WEEKDAY(A1)>=1,7)+1-WEEKDAY(A1)+A1))/7)+1+(WEEKDAY(A1)=1
))-SUMPRODUCT(--(HolidayRange>=A1),--(HolidayRange<=B1))

It can probably be improved on / made more efficient.
--
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Daniel.M]

=SUM(INT((A2-WEEKDAY(A2-{1;2;3;4;5;6})-A1+8)/7))-SUMPRODUCT(
ISNUMBER(MATCH(ROW(INDIRECT(A1&":"&A2)),Holidays,0))*(WEEKDAY(
ROW(INDIRECT(A1&":"&A2)))<>1))

Sorry. For the case where you do have holidays (and want to exclude them):

=SUMPRODUCT(ISNA(MATCH(ROW(INDIRECT(A1&":"&A2)),Holidays,0))*
(WEEKDAY(ROW(INDIRECT(A1&":"&A2)))<>1))

Regards,

Daniel M.

 

[Dana DeLouis]

This idea does not have as many options. Maybe the op could add any
holidays directly into the Networkdays function. The disadvantage here is
that it requires a vba library reference to "ATPVBAEN"
Uses Networkdays, and adds any Saturdays.

Function FindDays(dteStart As Date, dteEnd As Date) As Long
'// Dana DeLouis

Const Networkdays As String = "ATPVBAEN.XLA!Networkdays"
Dim wd1 As Long
Dim wd2 As Long
wd1 = Weekday(dteStart)
wd2 = Weekday(dteEnd)

FindDays = -(wd1 = 7 Or wd2 = 7 Or (wd2 < wd1))
FindDays = FindDays + (dteEnd - dteStart) \ 7
FindDays = FindDays + Run(Networkdays, dteStart, dteEnd)
End Function

 

[Bob Phillips]

Dana,

but what about if one of the days in the holiday range is a Saturday?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Dana DeLouis]

Opps! You're right. Didn't even think about that. Thanks. :>)

--
Dana DeLouis

but what about if one of the days in the holiday range is a Saturday?

<snip>

 

[Bob Phillips]

It occurred to me as I catered for it in my code (I hope<vbg>).

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Guest]

Great stuff chaps, thanks. But what about crossing a year change.

Your formulas give me 300 days between December and January of the following
year...!

Michael

 

[Vasant Nanavati]

Try this:

=NETWORKDAYS(A1,B1,HOLIDAYS)+INT((B1-A1)/7)+IF(WEEKDAY(A1)>6-MOD(B1-A1,7),1,
0)-SUM((WEEKDAY(HOLIDAYS)=6)*1)

Not guaranteed dto work; let us know how it turns out.