Networkingdays() an improved version of networkdays formula

Posted on June 9th, 2009 in Excel Howtos , Learn Excel , VBA Macros - 30 comments

Networkdays - Calculate working days between 2 given dates in excel

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

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

30 Responses to “Networkingdays() an improved version of networkdays formula”

  1. chrisbrooke says:

    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.

  2. Chandoo says:

    @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.

  3. Glenn says:

    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?

  4. Tanvir says:

    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.

  5. [...] if your working week is not from Monday to Friday, you can try the NETWORKINGDAYS() UDF in the same [...]

  6. [...] if your working week is not from Monday to Friday, you can try the NETWORKINGDAYS() UDF in the same [...]

  7. Jason says:

    Chandoo – Please show me how you would add the holiday list functionality to your UDF. Thanks for your reply!

  8. Chandoo says:

    @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.

  9. jiddings says:

    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?

  10. Chandoo says:

    @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

  11. Raj says:

    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

  12. Chandoo says:

    @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

  13. Anupam Dixit says:

    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.

  14. AP says:

    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!

  15. MCB says:

    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.

  16. Mahendra says:

    Superb and Thanks.

    I was just working on interest calculation and so was looking for the same and got it here.

  17. NEETA says:

    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.

  18. NEETA says:

    EL from 22/05/2011 to 05/06/2011.

  19. Abraham says:

    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.

    • Hui... says:

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

      • RoVhea says:

        But “=networdays.intl” is applicable only for Excel 2010 and latest version right? how about for Excel2007?

  20. EJGUN says:

    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

     

    • Jj says:

      Hi EJGUN!

      Can you please provide the link where I can download the add-in with your correction.

      Thanks!!!

      • Winnie says:

        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.

  21. utan says:

    In my company working days are from Monday to haft of Saturday. How can i use this function?

    Thanks!

  22. KOCHAN says:

    Why you are not using built in functions :

    NETWORKDAYS.INTL and WORKDAYS.INTL. .No neeed to write a user defined function

  23. AthanasiosZ says:

    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!

  24. as says:

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

    • Hui... says:

      @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

Leave a Reply