If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Workdays (inc Sat) formula
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 |
#3
|
|||
|
|||
Workdays (inc Sat) formula
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 |
#4
|
|||
|
|||
Workdays (inc Sat) formula
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 |
#5
|
|||
|
|||
Workdays (inc Sat) formula
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 |
#6
|
|||
|
|||
Workdays (inc Sat) formula
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 |
#7
|
|||
|
|||
Workdays (inc Sat) formula
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 |
#8
|
|||
|
|||
Workdays (inc Sat) formula
=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. |
#9
|
|||
|
|||
Workdays (inc Sat) formula
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 |
#10
|
|||
|
|||
Workdays (inc Sat) formula
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 |
|
Thread Tools | |
Display Modes | |
|
|