We all know that networkdays() an extremely powerful and simple excel formula can help you calculate no. of working days between 2 given dates.
But there is one problem with it. It assumes 5 day workweek starting with Monday to Friday. Not all countries have workweek from Monday to Friday. As Incubus wrote to me in an e-mail,
In Excel, the function “networkdays” doesn’t work for users in the middle east ‘cos it counts Saturday & Sunday as weekend. This is good enough only if you live in elsewhere, but for us weekend is Friday & Saturday.
This got me thinking and I ended up writing a user defined formula (UDF) to calculate working days between 2 given dates with any criteria. This will be good for calculating payrolls for temporary workers, offshore partners and of course people working countries where Saturday or Sunday or not usually holidays.
Please download the NetWorkingDays Add-in if you want to use this function.
If you are curious what is inside, see the UDF code
How to use the NetWorkingDays() UDF?
Once you download the add-in, just install the add-in by,
- [in Excel 2003] By going to Tools > Addins > Browse
- [in Excel 2007] By going to Office Button > Excel Options > Addins > “Go button” > Browse
- Specify the location where you saved the downloaded file
Now that the add-in is installed, you can use the UDF by writing a formula like this:
=networkingdays("01-06-2009","02-07-2019","12356")
The first argument is start date, the second one is end date and third one tells which days of week are working (Monday is 1 and Sunday is 7). So the above formula counts all the Mondays, Tuesdays, Wednesdays, Fridays and Saturdays between 01-06-2009 and 02-07-2019
Known Errors and Limitations
The formula returns #VALUE! error when you try really long durations (like trying to find all the workingdays between now and a century later)
Another thing is, you cannot feed a custom holiday list to this formula. But that is very easy to add on, so I didn’t bother.
So go ahead and give it a try
Download the NetWorkingDays() UDF Excel Add-in
and tell me what you think…
On dates and times in Excel: Date & Time Formulas | 10 Tips on Using Dates & Times in Excel
35 Responses to “Networkingdays() an improved version of networkdays formula”
Hi - It appears that to display the number of Mondays, Tuesdays and Wednesdays for example between two dates, using the string "123" as per the example, the first date has to be a Monday. Otherwise, for example, if the start date is a Thursday, the use of "123" returns the number of Thurday, Friday and Saturdays between the dates.
The update is required.
@Chrisbrooke: Thanks for your comments. I remember specifically testing for this scenario. I will once again test this and post a fix in the coming days. Thanks for telling me about this.
I have a date with time stamps. I want to take a time 8/1/09 12:00am and convert to 7/31/09. Any time after 12am until 7am I want to converrt back to previous day. Can you help?
In our country working days are Sunday to Wednesday.
Can you please provide me the UDF formula to calculate the working days between two date from two differnt cell.
[...] if your working week is not from Monday to Friday, you can try the NETWORKINGDAYS() UDF in the same [...]
[...] if your working week is not from Monday to Friday, you can try the NETWORKINGDAYS() UDF in the same [...]
Chandoo - Please show me how you would add the holiday list functionality to your UDF. Thanks for your reply!
@Jason: Thanks for the question,
Here is the basic algorithm.
loop through the list of holidays
if the holiday is between start and end days and if the holiday's day of week is a working day
decrease the return value by 1
do for next holiday
you can add it towards the end of the function.
If you do write it, share the code through comments so that others can also benefit from the same. All the best.
Dear Chandoo
Thanks for your formula. Please find an updated formula considering Friday, Saturday as weekends (indicated by 7) and excluding any holidays in between (refer to query of Jason).
The formual is as follows:
=NETWORKDAYS.INTL(DATE(2016,9,21),DATE(2016,10,20),7,{"2016/10/2","2016/10/11","2016/10/12"})
The above formula is working fine and using this for payroll attendance.
Appreciate your comments as you are a Guru in excel.
Chandoo - Any feedback on your comment June 22, 2009? "@Chrisbrooke: Thanks for your comments. I remember specifically testing for this scenario. I will once again test this and post a fix in the coming days. Thanks for telling me about this."
I tried =networkingdays("12-31-2008","12-31-2009","1234567") and it's returned value was 370. This does not appear correct as it should be returing 365, should it not?
@Jiddings... Thanks for the reminder. I solved the problem yesterday.
Use this code inside the function...
Dim totalDays, startDay As Integer
Dim curWeekday As Integer
Dim i, retVal As Integer
totalDays = eDate - sDate + 1
startDay = Weekday(sDate, vbMonday)
retVal = 0
For i = 1 To 7
curWeekday = (startDay - i + 7) Mod 7 + 1
If InStr(workingDays, CStr(i)) > 0 Then
retVal = retVal + Int(totalDays / 7)
If curWeekday < (totalDays Mod 7) + 1 Then retVal = retVal + 1 End If End If Next i networkingdays = retVal End Function
Hi i am using this formula to maitaing the leave of employees but ia m not getting it so can you help me to put formula in excel 2007 as Monday to Saturday as working day
waiting for the reply
@Raj.. Can you tell me where you are facing the problem? Also, try the code here: http://chandoo.org/wp/2009/06/09/networkingdays/#comment-117374
if i want to set only two days a week as working day say mon and wed . the function tells me net working days between the dates but i also want to get the dates to be appear. what shoud i do. i am a hooby class teacher giving twice a week classes. i want to mainain my record on computer in excell.
[...] you can use networkingdays() custom UDF [...]
Hi all, I love this cracker of a formula and use it at work (Excel 2003 & 2007), I would like to use it at home (Excel 2011 for Mac) is there an add-in version for Mac? I did some searching on Google but came up empty handed. Many thanks!
Chandoo,
I have reproduced till the last code that you have provide us, however, I have detected an error; for example: using 15-09-2011 till 22-09-2011 that is OK (=7) but when I used the Monday as start date the result is not OK, for example using 19-09-2011 till 22-09-2011 the result is equal to 3 instead 4. Do you have another version of the script?. Thanks in advance.
Superb and Thanks.
I was just working on interest calculation and so was looking for the same and got it here.
can u calculate balance of earned leave where 11 days make 1 earned leave by using excel formula ? Say for example, Sugandha joins office on 23/10/2005. She availed 10 days Earned leave from 03/02/2009 to 12/02/2009 and again 15 days EL from 22/05/2011 to 05/05/2011. So, what is his balance or accrued EL as on 31/03/2012.
Sick Leave from 09/08/2008 to 22/08/2008 =14 days
Sick Leave from 02/02/2010 to 18/02/2010 = 17 days
The maximum EL one can accumulate is 240 days.
Pls note that sick leave taken are not counted while earned leave is calculated.
EL from 22/05/2011 to 05/06/2011.
Help please. Say I have a start date of 20/05/12 so it is a known date, and I want to calculate working days from the specified date "a known date" till today's date stamp and do this calculation every day.
@Abraham
have a look at using the Workdays or Wordays.Intl function
eg:
=NETWORKDAYS.INTL(Start date, End Date)
or
=NETWORKDAYS.INTL(A1, A2)
Where A1 has the Start Date
so you can do:
=NETWORKDAYS.INTL(A1, Today())
But "=networdays.intl" is applicable only for Excel 2010 and latest version right? how about for Excel2007?
I tried using your UDF for NetWorkingDays and found, even after your correction, that it produced errors. After studying it for a while, I created a new version (hopefully code shows up below). That solves the problem and produces accurate counts. This version uses Ranges for inputs for start date, end date, and holidays. It still uses a string (e.g. "123") for working days. I hope you find it useful.
Eric
'==============================================================================
' F U N C T I O N NetWorkingDays
'==============================================================================
Option Explicit
'
' This function tells you how many working days are there between 2 given dates.
' You can use this to calculate working days in countries where saturday and sunday are
' not usually holidays.
'
Function NetWorkingDays(sDateRng As Range, eDateRng As Range, workingDays As String, _
Optional HolidayRng As Range) As Long
'
' e.g. use: =NetWorkingDays($A$1,$A$2,"12356")
' will tell you the count of mondays, tuesdays, wednesdays, fridays and saturdays
' between the dates in $A$1 and $A$2
'
' e.g. use: =NetWorkingDays($A$1,$A$2,"12356",$H$2:$H$50)
' will tell you the count of mondays, tuesdays, wednesdays, fridays and saturdays
' between the dates in $A$1 and $A$2, excluding any holidays (listed as dates) in
' the range $H$2:$H$50
'
' You can also count the number of individual days between dates:
' =NetWorkingDays($A$1,$A$2,"2")
' will tell you the number of tuesdays between those two dates
'
Dim sDate As Date, eDate As Date, Holidays() As Variant
Dim nHolidays As Long
Dim totalDays, startDay As Long, endDay As Long
Dim totWeeks As Long, extraDays As Long
Dim curWeekday As Long
Dim i, retVal As Long
'
If (Not IsDate(sDateRng.Value)) Then
sDate = 0#
Else
sDate = sDateRng.Value
End If
'
If (Not IsDate(eDateRng.Value)) Then
eDate = 0#
Else
eDate = eDateRng.Value
End If
'
If (Not HolidayRng Is Nothing) Then
nHolidays = HolidayRng.Cells.Count
ReDim Holidays(0 To nHolidays - 1)
Holidays = HolidayRng.Value2 ' CAREFUL - this automatically assumes dates in the range!
Else
nHolidays = 0
End If
'
totalDays = eDate - sDate + 1
startDay = Weekday(sDate, vbMonday)
endDay = Weekday(eDate, vbMonday)
'
' First calculate the number of whole weeks in the date span.
' Whole weeks are defined as all weeks that start on Monday and
' end on Sunday.
'
If (startDay = 1) Then
If (endDay = 7) Then
extraDays = 0
Else
extraDays = 7 - endDay
End If
Else
If (endDay = 7) Then
extraDays = 7 - startDay + 1
Else
extraDays = 7 - startDay + 1 + endDay
End If
End If
totWeeks = (totalDays - extraDays) / 7
'
' Now determine how many "extra" days are on either side of
' the whole weeks. Include only those "extra" days that are
' a part of the "workingDays" string.
'
extraDays = 0
If (startDay <> 1) Then
For i = startDay To 7
If (InStr(workingDays, CStr(i)) > 0) Then
extraDays = extraDays + 1
End If
Next i
End If
If (endDay <> 7) Then
For i = 1 To endDay
If (InStr(workingDays, CStr(i)) > 0) Then
extraDays = extraDays + 1
End If
Next i
End If
'
retVal = totWeeks * Len(workingDays) + extraDays
'
' Now subtract out the holidays if applicable to the period in question
'
If (nHolidays > 0) Then
For i = LBound(Holidays) To UBound(Holidays)
If (InStr(workingDays, Weekday(Holidays(i, 1), vbMonday)) > 0 And _
Holidays(i, 1) >= sDate And Holidays(i, 1) <= eDate) Then
retVal = retVal - 1
'Debug.Print Format(Holidays(i, 1), "dd mmm, yyyy") & " was removed: it is a " & Format(Holidays(i, 1), "dddd")
Else
'Debug.Print Format(Holidays(i, 1), "dd mmm, yyyy") & " was not removed: it is a " & Format(Holidays(i, 1), "dddd")
End If
Next i
End If
'
NetWorkingDays = retVal
End Function
Hi EJGUN!
Can you please provide the link where I can download the add-in with your correction.
Thanks!!!
Hi Chandoo & EJGUN!
You guys are awesome. Thank you very much for making our lives easier.
EJGUN
I copy and pasted your UDF but it is not compiling. Do you mind reposting the code or posting the fix, if you have done it since you originally posted?
Again thank you guys.
In my company working days are from Monday to haft of Saturday. How can i use this function?
Thanks!
Why you are not using built in functions :
NETWORKDAYS.INTL and WORKDAYS.INTL. .No neeed to write a user defined function
I agree with Kochan. By using NETWORKDATS.INTL, no UDF required.
Dear Chandoo,
I read your website the last months and I find it EXTRA awesome!!!!!
Now about this topic.
I need for a project to find the next business working day for an employee, who is working only Monday, Wednesday and Friday. For example the starting date is 05/7/2014 and the first task for example needs 3 days. So, the task will not finish on Friday 05/9/2014 but on Monday afternoon 05/12/2014. (I calculate and the starting day, because will start 05/7/2014 morning).
So, how I will appear that date (05/12/2014)?
Thank you very much in advanced!
The networking days is showing wrong result i.e. 18 days when the start date is 01-06-2014 and end date is 19-04-2014 and count days except friday i.e. 5.
=+networkingdays("01-06-2014","19-06-2014","123467")
for that matter counting all days between above days it shows 21 days
=+networkingdays("01-06-2014","19-06-2014","1234567")
@AS
You have used the wrong format for the weekends
it should be
=NETWORKDAYS.INTL("01/06/2014","19/06/2014","1111011")
for Except Friday
or
=NETWORKDAYS.INTL("01/06/2014","19/06/2014","1111111")
For no weekends
I have a question in this regard, in my office if one employee is absent on saturday and next monday, sunday (though is is weekly off) is treated as absent for that employee. Now is there any process to apply this in attendance.
In MS Excel 2007:
How can I calculate total days required to do a job considering the below conditions:
> if the start date of the job is 01-Jan-15 (suppose)
> End Date: 30-jun-15 (suppose)
> only one day (Friday) as a weekly holiday
> other govt. holiday (as per govt. circular mentioning somewhere in the sheet manually)?
Please help.