Workdays (inc Sat) formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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)
)
 
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)
 
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?
 
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)
 
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.
 
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.
 
=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))
Click to expand...

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.
 
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
 
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)
 
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?
Click to expand...

<snip>
 
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)
 
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
 
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.
 
Back
Top