Workdays (inc Sat) formula

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)
)

--
Regards
Frank Kabel
Frankfurt, Germany


x wrote:
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

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)

x wrote in message ...
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 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?

--
Regards
Frank Kabel
Frankfurt, Germany


Bob Phillips wrote:
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.



x wrote in message ...
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 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)

"Frank Kabel" wrote in message
...
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?

--
Regards
Frank Kabel
Frankfurt, Germany


Bob Phillips wrote:
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.



x wrote in message ...
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,

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.


x wrote in message ...
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 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

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

"Frank Kabel" wrote in message
...
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)
)

--
Regards
Frank Kabel
Frankfurt, Germany


x wrote:
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

=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)),Ho lidays,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 DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Bob Phillips" wrote in message
...
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)

"Frank Kabel" wrote in message
...
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?

--
Regards
Frank Kabel
Frankfurt, Germany


Bob Phillips wrote:
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.



x wrote in message ...
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

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" wrote in message
...
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 DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Bob Phillips" wrote in message
...
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)

"Frank Kabel" wrote in message
...
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?

--
Regards
Frank Kabel
Frankfurt, Germany


Bob Phillips wrote:
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.



x wrote in message ...
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