Calculating weekdays in Excel - WORKDAY and NETWORKDAYS functions

This short tutorial explains the use of Excel NETWORKDAYS and WORKDAY functions to calculate workdays with custom weekend parameters and holidays.

Microsoft Excel provides two functions specially designed for calculating weekdays - WORKDAY and NETWORKDAYS.

The WORKDAY function returns a date N working days in the future or in the past and you can use it to add or subtract workdays to a given date.

Using the NETWORKDAYS function, you can calculate the number of workdays between two dates that you specify.

In modem versions of Excel 2010, 2013 as well as coming Excel 2016, more powerful modifications of the above-said functions are available, WORKDAY.INTL and NETWORKDAYS.INTL, which let you define which and how many days are weekend days.

And now, let's have a closer look at each function and see how you can use it to calculate working days in your Excel worksheets.

Excel WORKDAY function

The Excel WORKDAY function returns a date that is a given number of working days ahead of or prior to the start date. It excludes weekends as well as any holidays you specify.

The WORKDAY function is purposed for calculating workdays, milestones and due dates based on the standard working calendar, with Saturday and Sunday being the weekend days.

WORKDAY is a built-in function in Excel 2007, 2010, 2013 and Excel 2016. In earlier versions, you need to enable the Analysis ToolPak.

When using WORKDAY in Excel, you have to input the following arguments:

WORKDAY(start_date, days, [holidays])

The first 2 arguments are required and the last one is optional:

  • Start_date - the date from which to start counting weekdays.
  • Days - the number of workdays to add to / subtract from start_date. A positive number returns a future date, a negative number returns a past date.
  • Holidays - an optional list of dates that should not to be counted as working days. This can be either a range of cells containing the dates you want to exclude from calculations, or an array constant of the serial numbers representing the dates.

Now that you know the basics, let's see how you can use the WORKDAY function in your Excel worksheets.

How to use WORKDAY to add / subtract business days to date

To calculate workdays in Excel, follow these simple rules:

  • To add workdays, enter a positive number as the days argument of a WORKDAY formula.
  • To subtract workdays, use a negative number in the days argument.

Supposing you have a start date in cell A2, a list of holidays in cells B2:B5, and you want to find out the dates 30 workdays in the future and past. You can do this using the following formulas:

To add 30 workdays to the start date, excluding holidays in B2:B5:

=WORKDAY(A2, 30, B2:B5)

To subtract 30 workdays from the start date, excluding holidays in B2:B5:

=WORKDAY(A2, -30, B2:B5)

To calculate weekdays based on the current date, use the TODAY() function as the start date:

To add 30 workdays to today's date:


To subtract 30 workdays from today's date:


To supply the start date directly to the the formula, use the DATE function:

=WORKDAY(DATE(2015,5,6), 30)

The following screenshot demonstrates the results of all these and a few more WORKDAY formulas:
Using the WORKDAY function in Excel

And naturally, you can enter the number of workdays to add to / subtract from the start date in some cell, and then refer to that cell in your formula. For example:


Where A2 is the start date and C2 is the number of non-weekend days behind (negative numbers) or ahead of (positive numbers) the start date, no holidays to exclude.
Excel WORKDAY formula example

Excel WORKDAY.INTL function

WORKDAY.INTL is a more powerful modification of Excel WORKDAY function that works with custom weekend parameters. As well as WORKDAY, it returns a date that is a specified number of workdays in the future or in the past, but lets you determine which days of the week should be considered weekend days.

The WORKDAY.INTL function was introduced in Excel 2010 and so is not available in earlier Excel versions.

The syntax of the Excel WORKDAY.INTL function is as follows:

WORKDAY.INTL(start_date, days, [weekend], [holidays])

The first two arguments are required and are akin to WORKDAY's:

Start_date - the initial date.

Days - the number of working days before (negative value) or after (positive value) the start date. If the days argument is supplied as a decimal number, it is truncated to the integer.

The last two arguments are optional:

Weekend - specifies which weekdays should be counted as weekend days. This can be either a number or a string, as demonstrated below.

Number Weekend days
1 or omitted Saturday, Sunday
2 Sunday, Monday
3 Monday, Tuesday
4 Tuesday, Wednesday
5 Wednesday, Thursday
6 Thursday, Friday
7 Friday, Saturday
11 Sunday only
12 Monday only
13 Tuesday only
14 Wednesday only
15 Thursday only
16 Friday only
17 Saturday only

Weekend string - a series of seven 0's and 1's that represent seven days of the week, beginning with Monday. 1 represents a non-working day and 0 represents a workday. For example:

  • "0000011" - Saturday and Sunday are weekends.
  • "1000001" - Monday and Sunday are weekends.

At first sight, weekend strings may seem superfluous, but I personally like this method better because you can make a weekend string on the fly without having to remember any numbers.

Holidays - an optional list of dates you want to exclude from the working day calendar. This can be a range of cells containing the dates, or an array constant of the serial values representing those dates.

Using WORKDAY.INTL in Excel - formula examples

Well, the pretty big bulk of theory we've just discussed may seem quite complicated and confusing, but trying your hand at formulas will make things really easy.

On our dataset, with the start date in cell A2 and a list of holidays in A5:A8, let's calculate workdays with custom weekends.

  • To add 30 workdays to the start date, Friday and Saturday counted as weekends and holidays in A5:A8 excluded:=WORKDAY.INTL(A2, 30, 7, A5:A8)
    =WORKDAY.INTL(A2, 30, "0000110", A5:A8)
  • To subtract 30 workdays from the start date, Sunday and Monday counted as weekends and holidays in A5:A8 excluded:=WORKDAY.INTL(A2, -30, 2, A5:A8)
    =WORKDAY.INTL(A2, -30, "1000001", A5:A8)
  • To add 10 workdays to the current date, Sunday being the only weekend day, no holidays:=WORKDAY.INTL(TODAY(), 10, 11)
    =WORKDAY.INTL(A2, 10, "0000001")

In your Excel sheet, the formulas may look similar to this:

Using the WORKDAY.INTL in Excel to calculate workdays in custom weekend parameters

Note. Both Excel WORKDAY and WORKDAY.INTL functions return serial numbers representing the dates. To get those numbers displayed as dates, select the cells with the numbers and press Ctrl+1 to open the Format Cells dialog. On the Number tab, select Date in the Category list, and choose the date format you want. For the detailed steps, please see How to change date format in Excel.


If your Excel WORKDAY or WORKDAY.INTL formula returns an error, the reason is likely to be one of the following:

#NUM! error occurs if either:

  • a combination of the start_date and days arguments results in an invalid date, or
  • weekend argument in the WORKDAY.INTL function is invalid.

#VALUE! error occurs if either:

  • start_date or any value in holidays is not a valid date, or
  • days argument is non-numeric.

Excel NETWORKDAYS function

The NETWORKDAYS function in Excel returns the number of workdays between two dates, excluding weekends and, optionally, the holidays you specify.

The syntax of Excel NETWORKDAYS is intuitive and easy-to-remember:

NETWORKDAYS(start_date, end_date, [holidays])

The first two arguments are obligatory and the third one is optional:

  • Start_date - initial date from which to start counting working days.
  • End_date - the end of the period for which you are counting workdays.

Both the start date and end date are counted in the returned number of workdays.

  • Holidays - an optional list of holidays that should not to be counted as work days.

How to use NETWORKDAYS in Excel - formula examples

Let's say you have a list of holidays in cells A2:A5, start dates in column B, end dates in column C, and you want to know how many workdays are between these dates. The appropriate NETWORKDAYS formula is easy to figure out:

=NETWORKDAYS(B2, C2, $A$2:$A$5)

Notice that the Excel NETWORKDAYS function returns a positive value when the start date is less than the end date, and a negative value if the end date is more recent than the start date (as in row 5):
Using the NETWORKDAYS function in Excel


Like NETWORKDAYS, Excel's NETWORKDAYS.INTL function calculates the number of weekdays between two dates, but lets you specify which days should be counted as weekend days.

The syntax of the NETWORKDAYS.INTL function is very similar to NETWORKDAYS', except it has the additional [weekend] parameter that indicates which days of the week should be counted as weekends.

NETWORKDAYS.INTL( start_date, end_date, [weekend], [holidays] )

The weekend argument can accept either a number or a string. The numbers and weekend strings are exactly the same as in the weekend parameter of the WORKDAY.INTL function.

The NETWORKDAYS.INTL function is available in modem versions of Excel 2010, 2013 and Excel 2016.

Using NETWORKDAYS.INTL in Excel - formula examples

Using the list of dates from the previous example, let's calculate the number of workdays between two dates with Sunday being the only weekend day. For this, you type number 11 in the weekend argument of your NETWORKDAYS.INTL formula or make a string of six 0's and one 1 ("0000001"):

=NETWORKDAYS.INTL(B2, C2, 11, $A$2:$A$5)


=NETWORKDAYS.INTL(B2, C2, "0000001", $A$2:$A$5)

The following screenshot proves that both formulas return absolutely identical results.

Using the NETWORKDAYS.INTL function is Excel

How to highlight workdays in Excel

Using the WORKDAY and WORKDAY.INTL functions, you can not only calculate workdays in your Excel worksheets but also highlight them as your business logic requires. For this, you create a conditional formatting rule with either a WORKDAY or WORKDAY.INTL formula.

For example, in a list of dates in column B, let's highlight only future dates that are within 15 workdays from today's date, excluding two holidays in cells A2:A3. The most obvious formula that comes to mind is as follows:

=AND($B2>TODAY(), $B2<=WORKDAY(TODAY(), 15, $A$2:$A$3))

The first part of the logical test cuts off past dates, i.e. you check if a date is equal to or greater than today: $B2>TODAY(). And in the second part, you verify whether a date is no more than 15 weekdays in the future, excluding the weekend days and specified holidays: $B2<=WORKDAY(TODAY(), 15, $A$2:$A$3)

The formula looks correct, but once you create a rule based on it, you will realize that it highlights wrong dates:
Using the WORKDAY function in Excel conditional formatting

Let's try to figure out why that happens. The problem is not with the WORKDAY function, as someone may conclude. The function is right, but... what does it actually do? It returns a date 15 workdays from now, excluding weekend days (Saturday and Sunday) and holidays in cells A2:A3.

Okay, and what does the rule based on this formula do? It highlights ALL the dates that are equal to or greater than today and less than the date returned by the WORKDAY function. You see? All the dates! If you don't want to color the weekends and holidays, then you need to explicitly tell Excel not to. So, we are adding two more conditions to our formula:

As demonstrated in the below screenshot, the improved formula works perfectly:

=AND($B2>TODAY(), $B2<=WORKDAY(TODAY(), 15, $A$2:$A$3), COUNTIF($A$2:$A$3, $B2)=0, WEEKDAY($B2, 2)<6)
Highlighting workdays in Excel

As you see, the WORKDAY and WORKDAY.INTL functions make calculating workdays in Excel quick and easy. Of course, your real-life formulas are likely to be more sophisticated, but knowing the basics helps immensely, because you can remember only a small set of essential things and derive the rest. I thank you for reading and hope to see on our blog next week!

You may also be interested in

Category: Excel Tips

Table of contents

90 responses to "Calculating weekdays in Excel - WORKDAY and NETWORKDAYS functions"

  1. Paulo says:

    I am having difficulties understandig the logic behind NETWORKDAYS being unable to return 0 (zero) working days when one uses the very same dates for start_date and end_date.

    Also, WORKDAYS seems inconsistent with NETWORKDAYS as it allows 0 (zero) as its "days" argument.

    Can you help me?

    • Hello, Paulo,

      This is the way the two functions are implemented. Sorry, it's hard to say why Microsoft decided not to coordinate them.

    • Irene says:

      Hello Paulo, you need to include -1 at the end of the formula. this is because excel calculation start calculate the start day. so you will get 1 day even if your start day and end day is the same day. example : =NETWORKDAYS(P12;C12)-1

      • Glen says:

        So adding the -1 is helpful for dates that land on a weekday, but for dates that land on a weekend the formula already has the weekend exclusion built in so if you add the -1 to the end of the formula it will subtract an extra day that you don't want subtracted. How do you solve for this when you don't want to include the start date in the count?

  2. Lauren says:


    What if we WANT to include the weekends? So our total workdays would be 7 instead of 5..

    • CC says:

      I was looking for this also and got this answer from a co-worker. You just add the number of days you need from the date in the cell, ex. =A1+25 and it should return the date that is 25 days from the date you entered. Worked for me.

  3. Shiva says:


    Am working with =WORKDAY.INTL(L6+1,C32/7.5,11,Sheet1!B2:B16) as listed below for project schedules the problem noticed is with the weekend when listed as 11 its not calculating as desired see in the month of march 27th is sunday. Even after assigning the 11 the result is given as 27th march 2016. which as per above weekends dates should result as 28th-Mar-2016.. Please review and suggest a solution. Thank you.

  4. Shiva says:

    Hello All,

    Any review update on my above listed query,

    Were, the parameters are as listed below;
    L6 = contains 1st task completed date 26-Mar-2016
    = for which i added 1 day, assuming that next 2nd task will start on the next day as L6 + 1
    C32= is the estimated amount of effort needed to complete 2nd task in hours, which again is converted to days by dividing with 7.5 as C32/7.5
    sheet1!B2:B16= contains yearly official exception holidays.....Kindly review and confirm a solution....Once again thank you...

  5. Taylor says:

    My results for the NETWORKDAYS formula is not showing up as a numerical number. Rather in a date format m/d/yy.

    Start Date: 3/24/16
    End Date: 8/1/16
    Result: 4/2/00 <-- I don't know what this means.

    In both the start and end dates, I put in the date function =DATE(YYYY,M,D)

    Do I have my cells formatted wrong? How can I get my results to show as a number?

    • Mack says:


      I ran into that default issue. Just need to format cells as a number to see your results.

      Hope that help ---Cheers!

  6. Irfan Rasheed says:


    I have scenario here. I have some engineers, who are working in two countries every month. I need to get available hours of each engineer in each country. Problem is, public holidays and working days of both countries are different. say in Country A, Saturday and Sunday are off, whereas in other country Friday and Saturday are off. similarly, public holidays are also different. Can someone guide me what logic should I use to cater this problem.

    Irfan Rasheed

    • poornima says:


      AS3,AT3 = Start date, End date respectively
      $AY$3:$AY$4 = Range of holidays
      "17:30"-"8:30" = Shift end time to start time

      Worked for us!!!

      • Mohamed Gaber says:

        I have updated this formula as below to got days and hours

        =((NETWORKDAYS.INTL(C5,D5,7,$B$2:$B$19)-1)*("18:00"-"9:00")+IF(NETWORKDAYS.INTL(D5,D5,7,$B$2:$B$19),MEDIAN(MOD(D5,1),"18:00","9:00"),"18:00")-MEDIAN(NETWORKDAYS.INTL(C5,C5,7,$B$2:$B$19)*MOD(C5,1),"18:00","9:00"))&" Days "&HOUR((NETWORKDAYS.INTL(C5,D5,7,$B$2:$B$19)-1)*("18:00"-"9:00")+IF(NETWORKDAYS.INTL(D5,D5,7,$B$2:$B$19),MEDIAN(MOD(D5,1),"18:00","9:00"),"18:00")-MEDIAN(NETWORKDAYS.INTL(C5,C5,7,$B$2:$B$19)*MOD(C5,1),"18:00","9:00"))&" Hours"

        But the format is wrong like .0522225 Days 1 Hours

  7. Karan says:

    Great it works. Thanks for providing with examples.

  8. Jenny says:

    I am also trying to figure out how to use this while including weekend days (7 day work week). Essentially I'm in a long-term medical facility and we want to know how many dates patients are served between admission and resolution of medical issue, but this can occur on weekends as well. I can't just add dates as in the example above for the same question, because the admission dates are not consistent to always add 2 days for the weekend, for instance. I'm not sure how to create a rule to bypass this situation. (I tried inputting 0000000 for weekend parameters, but it didn't work).


    • Rizzy says:

      Refer to this reply:

      CC says:
      February 9, 2016 at 6:07 pm
      I was looking for this also and got this answer from a co-worker. You just add the number of days you need from the date in the cell, ex. =A1+25 and it should return the date that is 25 days from the date you entered. Worked for me.

  9. GM says:

    I have a scenario where I want to count the number of WORKING days a contractor has been on site from a start date to today (excluding weekends and public holidays), and then how many days of these that there has been works happening on site.

    I have set up the data in a table, with running dates in column A(i.e. as a diary type page in format Thursday,17th March 2016
    ), highlighted the dates in Red that are public holidays, then column B showing a number (0= men on site, 1= 1 man on site etc)
    Can you help?

  10. GSR says:

    dear sir / madam,

    could you pl give me an idea, how to find the number of days between the days,

    saturday - thursday = 5

    Thursday - Wednessday = 6


  11. Dwarkanath bari says:

    For Above Example if for all process on Air cooler product required 25 hrs which is into qty then from current date what is the actual target date for air cooler that date should be shown in targeted date. At the time of calculation of all hrs consider above points :

    1.Total Working Hrs are 8

    2.Saturday is the Weekly Off.

    3.If holiday in between then holidy list also maintain

  12. says:

    I'm hoping for a solution to calculate the number of NETWORKDAYS between two dates, but to only display a value of zero or greater.

    Example: we want to count the number of days the contract delayed start of a project.

    Value A would be date of process kickoff.
    Value B represents date worker completed their paperwork.
    Value C represents when the contract was signed.
    We are trying to count how many days from when value B occurred until Value C.

    In many cases Value C happened first so it did not cause a delay. Therefore i need the Formula to return a value of zero rather than a negative number.

  13. EMILY says:

    =IFERROR(NETWORKDAYS([@Received],[@[todays''s date]])-1,"N/A") I am using this fomrula to calculate a work date, and where there are blanks I would like it say N/A but it is placing "30472" on the line, how do I correct that?

  14. David says:

    I am trying to calculate the difference between a start date(arrives)and time to finish date/time (goes to workshop), I need to exclude weekends also for example
    start date: 20/10/2016 10:45 (formatted in one cell)
    finish date: 25/10/2016 11:30 (formatted in one cell)
    I need to show complete total days, hours and mins (4d 0h 45m)
    I have put this together with a little help but is quite right:=NETWORKDAYS(A13,B13)&" days "&TEXT(B13-A13,"h"" h ""m"" m """)
    if the start date and finish date are the same and the only difference is the time/ hour I keep getting 1 day plus the hours (1d 3h 45m not 0d 3h 45m as I need)
    any help would be great, thanks, David

  15. Nicole Lee says:

    How is the formula look like if i would like to count two separate weekdays excluding hoiliday between a range date in one formula?

    Is it possible?

    Thank you for your help

  16. Matt Teuteberg says:

    I am currently using the below formula in conditional formatting to shade today's date and the next 5 days as yellow in a column of like 30 dates. Since the weekends are never listed, can we make those days not factor in so the count can go Friday to Monday and continue shading conditionally?

    =AND(T1>TODAY() +1,T1<=(TODAY()+5))

    If it can count Friday as Day 1, Monday as Day 2, Tuesday as Day 3, etc.

  17. Matt Teuteberg says:

    And if it could apply to the whole document that would work too :)

  18. Steve says:

    Hi Guys / Gals,

    I have learned alot here from this forums. So now, am having difficulties on how to computer for days that are adjustable.
    networkdays(today's date, end date, (holidays if any))

    Now what I want is for example the project finish early compare to the end date so how will it automatically adjust the dates. Thanks...

  19. Mima says:


    how can I calculate future days in excel excluding holidays and weekends? But I have to have real days in months ( 30 or 31)... do the function where I put 30 days doesn't help... example. start day is November 4th, I need date in 1y? and it should be November 6th 2017 because 4th and 5th is weekend. Hope you understood my question :)

  20. andres says:

    I want to calculate the days of a specific task. sometimes the task has not been done but I would like to develop a function that when the cell is blank to automatically use the current day. Can somebody help me with this.

  21. Stoure says:

    I ve found your tutorial very interresting. Right now I'm trying to dispay days of a month per week(a table for each week(from monday to sunday) of the month.
    A separate table for each week dispalying the day and the date with a title for each week(week from .... To ...).
    Hope someone can help.

  22. Sameh Reda says:

    i need a function to get the Actual Number of days providing Working Days
    Suppose the Working Days = 5 so the actual Number f days should be 7 because there is two days weekend

    another example :
    suppose working days = 10 so the actual number of days should be 14 because there are 4 days weekend
    so my question i need equation to calculate actual number of days when i entered the working days

  23. Ryan says:

    I need some assistance. I am creating a spreadsheet to calculate budgetary allowance on a daily basis. I have successfully set it using the following formula:
    A1= Monthly Budget for a given Expense account.


    I have just been informed since this is for work, it must exclude weekends. Please advise how to achieve this.

  24. Bapsy says:

    I need assistance and I don't find a way to eliminate 30536 when I use formula eg. Networkdays(B$2,today())and format as number its display 30536 if there is no date on cell B2. So how to make display zero for instance when there is no value on cell B2?


  25. morteza says:

    Assume working 11:15 hours/day and 17 days/month,since i'm beginner in excel I use this simple formula(17*11.25) to get the result,but the result is shown in decimal.I want to display result in time hh:mm format.

  26. Dale says:

    Is there a way to have the number of days worked show as 0 until and end date has been entered using the networkdays.intl formula. I know the start dates for my projects but the end dates are not know until the jobs are finaled.

  27. Becc says:

    Hi, can anyone please tell me how I can add calendar days excluding bank holidays to a date in excel?

    For example, if todays date is 16 feb 2017 and I want to add 90 calendar days excluding the bank holidays on 14&17 April and 1 May the date returned should be 19 May 2017.

    Thanks in Advance

  28. Alex says:

    Hi, Please tell me how could I add Saturday as working day and remove holidays. I want to use networkdays or workday formula.

  29. Raheel Choudary says:

    how would i include weekends and exclude holidays between 2 date time values (in order to find the total hours)?

  30. Majeed says:

    can I use 0000000 to make all days workdays?
    If not how can do so? I have been trying to crate a formula to calculate only holidays without weekends.
    Pls help ASAP.

  31. Joshua says:

    Hi there!
    Pls help me!
    I need from the below data: Week over Week growth calendar week 5 vs calendar week 4

    Date Sales
    A column B columns
    1. 01.01.2017 $3 309
    2. 2.01.2017 $1 475
    3. 03.01.2017 $3 012
    4. 04.01.2017 $4 667
    5. 05.01.2017 $8 884
    6. 06.01.2017 $3 718
    7. 07.01.2017 $6 234
    8. 08.01.2017 $7 957
    9. 09.01.2017 $1 485 $1 068 $2 396
    12.01.2017 $8 970
    13.01.2017 $7 310
    14.01.2017 $5 239
    15.01.2017 $9 238
    16.01.2017 $4 030
    17.01.2017 $2 274
    18.01.2017 $351
    19.01.2017 $9 026
    20.01.2017 $4 583
    21.01.2017 $9 467
    22.01.2017 $4 026
    23.01.2017 $1 269
    24.01.2017 $3 017
    25.01.2017 $1 448
    26.01.2017 $3 991
    27.01.2017 $981
    28.01.2017 $1 774
    29.01.2017 $8 218
    30.01.2017 $1 363
    31.01.2017 $1 978
    01.02.2017 $9 518
    02.02.2017 $6 790
    03.02.2017 $685
    04.02.2017 $9 659
    05.02.2017 $2 308
    06.02.2017 $2 943
    07.02.2017 $9 900
    08.02.2017 $2 878
    09.02.2017 $4 800
    10.02.2017 $5 779
    11.02.2017 $2 980
    12.02.2017 $8 762
    13.02.2017 $4 518
    14.02.2017 $4 679
    15.02.2017 $9 529
    16.02.2017 $3 730
    17.02.2017 $8 568
    18.02.2017 $2 543
    19.02.2017 $5 149
    20.02.2017 $5 509
    21.02.2017 $975
    22.02.2017 $6 952
    23.02.2017 $7 348
    24.02.2017 $3 871
    25.02.2017 $439
    26.02.2017 $4 947
    27.02.2017 $3 096
    28.02.2017 $1 096
    01.03.2017 $2 791
    02.03.2017 $9 353
    03.03.2017 $4 204
    04.03.2017 $5 514
    05.03.2017 $2 608
    06.03.2017 $3 973
    07.03.2017 $262
    08.03.2017 $7 098
    09.03.2017 $9 844
    10.03.2017 $2 102
    11.03.2017 $2 959
    12.03.2017 $2 185
    13.03.2017 $8 785
    14.03.2017 $9 152
    15.03.2017 $8 076
    16.03.2017 $5 308
    17.03.2017 $6 756
    18.03.2017 $5 623
    19.03.2017 $8 424
    20.03.2017 $1 227
    21.03.2017 $7 145
    22.03.2017 $6 089
    23.03.2017 $7 003
    24.03.2017 $6 873
    25.03.2017 $9 431
    26.03.2017 $8 849
    27.03.2017 $61
    28.03.2017 $6 979
    29.03.2017 $4 274
    30.03.2017 $6 166
    31.03.2017 $1 223
    01.04.2017 $181
    02.04.2017 $7 452
    03.04.2017 $1 103
    04.04.2017 $7 872
    05.04.2017 $7 976
    06.04.2017 $4 200
    07.04.2017 $4 077
    08.04.2017 $643
    09.04.2017 $2 103
    10.04.2017 $233
    11.04.2017 $8 710
    12.04.2017 $2 415
    13.04.2017 $255
    14.04.2017 $4 554
    15.04.2017 $6 194
    16.04.2017 $8 642
    17.04.2017 $563
    18.04.2017 $1 431
    19.04.2017 $9 061
    20.04.2017 $4 752
    21.04.2017 $5 270
    22.04.2017 $3 376
    23.04.2017 $3 367
    24.04.2017 $4 157
    25.04.2017 $9 120
    26.04.2017 $4 695
    27.04.2017 $180
    28.04.2017 $1 226
    29.04.2017 $9 717
    30.04.2017 $8 453
    01.05.2017 $1 335
    02.05.2017 $8 180
    03.05.2017 $5 871
    04.05.2017 $7 871
    05.05.2017 $3
    06.05.2017 $8 691
    07.05.2017 $5 905
    08.05.2017 $7 820
    09.05.2017 $3 609
    10.05.2017 $880
    11.05.2017 $2 288
    12.05.2017 $8 317
    13.05.2017 $2 901
    14.05.2017 $505
    15.05.2017 $7 818
    16.05.2017 $4 521
    17.05.2017 $9 606
    18.05.2017 $7 281
    19.05.2017 $8 224
    20.05.2017 $4 532
    21.05.2017 $1 131
    22.05.2017 $3 143
    23.05.2017 $5 627
    24.05.2017 $7 338
    25.05.2017 $5 833
    26.05.2017 $3 201
    27.05.2017 $4 831
    28.05.2017 $5 937
    29.05.2017 $4 099
    30.05.2017 $9 649
    31.05.2017 $6 475
    01.06.2017 $7 013
    02.06.2017 $8 710
    03.06.2017 $7 311
    04.06.2017 $9 534
    05.06.2017 $664
    06.06.2017 $1 174
    07.06.2017 $3 549
    08.06.2017 $9 282
    09.06.2017 $3 163
    10.06.2017 $9 396
    11.06.2017 $5 389
    12.06.2017 $8 069
    13.06.2017 $4 645
    14.06.2017 $515
    15.06.2017 $7 329
    16.06.2017 $3 507
    17.06.2017 $7 337
    18.06.2017 $8 276
    19.06.2017 $4 483
    20.06.2017 $6 745
    21.06.2017 $7 505
    22.06.2017 $9 470
    23.06.2017 $3 293
    24.06.2017 $1 621
    25.06.2017 $5 779
    26.06.2017 $5 039
    27.06.2017 $539
    28.06.2017 $9 198
    29.06.2017 $8 276
    30.06.2017 $6 275
    01.07.2017 $37
    02.07.2017 $5 744
    03.07.2017 $3 026
    04.07.2017 $2 438
    05.07.2017 $9 969
    06.07.2017 $968
    07.07.2017 $1 581
    08.07.2017 $3 515
    09.07.2017 $4 611
    10.07.2017 $8 988
    11.07.2017 $3 795
    12.07.2017 $2 751
    13.07.2017 $3 466
    14.07.2017 $1 479
    15.07.2017 $8 354
    16.07.2017 $8 552
    17.07.2017 $9 606
    18.07.2017 $8 746
    19.07.2017 $5 066
    20.07.2017 $1 178
    21.07.2017 $1 435
    22.07.2017 $6 682
    23.07.2017 $8 641
    24.07.2017 $2 762
    25.07.2017 $9 519
    26.07.2017 $2 342
    27.07.2017 $6 555
    28.07.2017 $3 210
    29.07.2017 $2 487
    30.07.2017 $2 332
    31.07.2017 $2 263
    01.08.2017 $3 346
    02.08.2017 $1 640
    03.08.2017 $8 744
    04.08.2017 $6 438
    05.08.2017 $8 156
    06.08.2017 $6 436
    07.08.2017 $3 891
    08.08.2017 $7 200
    09.08.2017 $4 455
    10.08.2017 $8 860
    11.08.2017 $9 157

  32. Rico says:

    Good day to you all, I need help please.
    Given the durations, I wanted to indicate the start and finish dates of each activity with the following conditions.

    Assuming start of first activity on 07 July 2017
    Start hour - 8:00AM
    End Working hour per day - 18:00
    Sunday and Holiday - No work.

    Hope to receive your support guys. Thanks in advance.

  33. Brecht Kapittel says:

    Thank you so much for this explanation, it helped a lot! I am using the WORKDAY function and want to include holidays. What if you want to select multiple columns with holidays, for example column B and column C? Is it possible to add this to the formula?

  34. santhakumar says:


    I want to format an excel sheet, need the result of number of working days by providing start date and end date.but the number of working days is 6 instead of 5. i tried with =networkdays, it is calculating 5 days per week.
    this is for my company, to prepare the planning schedule. so, here 6 days per week and add all the national holidays in holidays list.
    it would be great, if you can guide me, first to set a calendar with all national holidays and all Fridays as leave. then if i make a schedule with refer this calendar, then we can prepare the correct schedule with proper working days.

    thank you

  35. pilisi says:

    I like to remove the holidays. How would I get the end date? If I would give you give you the start date and the number of working days.

  36. pilisi says:

    I like to remove the holidays. How would I get the end date? If I would give you give you the start date and the number of working days.
    Yeah I said it but I didn't get the answer or formula for it. Thanks

  37. Lakshmi Hari says:

    I have two dates
    start date : 16-08-2017 10:00
    end date : 25-08-2017 : 18:00
    the work timings are from 09:00 to 18:00 hrs.
    The NETWORKDAY(start_day, end_date, list_of_aug_weekends) is returning me exactly 8 days which is correct. multiply it by 8, will get 64 hours. but can any one please help me with a formulae on getting 63 hours.

  38. amir says:

    I need a formula to add 3 days to my below date and time and exclude weekend:

    16-08-2017 10:00

    Any idea? workday formula just adds 3 days and make the time 12 A.M. and does not consider the time.

  39. amir says:

    I think I figured it out:


    Any feedback?!

  40. WH says:

    Hi Dear,
    10/10/2017 is my last work day. I need to calculate total day has worked.(from 1-10th). however, I need to exclude weekend and Public holiday. what formula I should use?

  41. Jessica says:

    I need to calculate days a sample is overdue, and networkdays -1 works just fine unless the sample is approved on a weekend day. For example, a sample is due on Friday, and is approved on Saturday or Sunday. Just using NETWORKDAYS(W2,P2,)-1,0) returns 0 days late. But I need it to be 1, since technically it is approved after the due date. Any ideas?
    Thank you for any help!

  42. Chris says:

    I have a spreadsheet with metrics for closed tickets. I want my column for "age" to be business days only, it currently includes weekends. I have a column with the opened date, and a column for the closed date. I tried plugging in NETWORKDAYS(start_date, end_date)but nothing changed.

    Any help would be greatly appreciated.

  43. mei says:

    how can I Combine this in one sheet.


  44. mei says:

    How can I combine this in one Column.



  45. Leesh says:

    HELP PLS! I have a list of dates in Coulmn L and then a list of dates in Cloumn M. I want to be able calculate network days between L:L and M:M and if the difference is 3 don’t count. I then want to sum all the 1’s! I would like to do this all in one formula and without a helper column if possible. I have over the 1000 dates in column L and the same in coulmn M. Is this possible?

  46. Cain says:

    Thanks!! Worked.

  47. Days says:

    a1 sell add date 1.1.2018 and b1 sell add 31.1.2018. then i know in c1 sell (a1 sell to b1 sell) how days Friday. what is the formula. please send me the details...

  48. M says:


    I am using the "=NETWORKDAYS(A1,B1)-1" function to count working days between two dates. A lot of sites I've looked at say that this formula should give me a "1" if the date for start and end is the same, but I am getting a "0". How can I make the same day turnaround show a "1"? This way dates that are the same day or one day after will return a "1" (i.e. 7/11/2018 to 7/11/2018 = 1 day and 7/11/2018 to 7/12/2018 = 1). Please let me know.

    • Doug says:

      NETWORKDAYS counts the number of work days from the start date as a day. The formula that you are using "=NETWORKDAYS(A1,B1)-1" is built to subtract the current date from the result. So, when =NETWORKDAYS(A1,B1) using 7/11/2018 to 7/11/2018 would ordinarily return a 1 it will return a 0. The idea being that the person who uses that formula wants to say that there are 0 days difference from 7/11 to 7/11.
      If you use the formula "=NETWORKDAYS(A1,B1)-1" and 7/11/2018 to 7/12/2018 the result will be 2 days minus 1 day which leaves 1 day.
      It sounds as if you should use the regular =NETWORKDAYS(A1,B1) formula to get the result you want.
      Keep in mind the standard NETWORK days function won't count weekends so 7/13/18 to 7/15/18 will be 1 workday.
      You can also include holidays in the calculation if you have a list of dates to use as holidays. The standard NETWORKDAYS function looks like this (startdate,enddate,[holiday]) with holiday being an option where you can enter the address of your holiday list.

  49. Jose says:

    I need to calculate number of days between today minus Holidays and Weekends and Dates in the past.
    10/26/2017-Today()-Holidays & Weekends

  50. Irma D says:

    Two dates on a job. Using NETWORKDAYS formula gives me a positive number. Since the project was completed early, how can I make the results a negative number (I.e -5)
    Due date 8/7/18
    Completed 7/31/18
    Results: 5 day

    • Iván Bustama says:

      Hi Irma D.
      you can include an "IF" formula to force the "NETWORKDAYS" result as you need,
      A1: Due date 8/7/18
      A2: Completed 7/31/18

  51. Suhail says:

    How do i get workday from a date, e,g what is the workday on 12th December 2018 if Saturday and sunday are weekends?
    Pls help

  52. Doug says:

    The function TEXT used in this formula will return the day name from a date. Where the date is in J10 it looks like:

  53. Kevin says:

    I am trying to calculate the difference between two dates. If date 1 is 2/18/2019 and date 2 is 2/19/2019 i get the difference of 2. But for my purposes it should be 0, because essentially the 2nd date was entered of the data was entered on 2/18/2019 but only provided to my org on 2/19/2019. I could do -2 but then it does not work when Date 1 lands on a weekend because I am using NETWORKDAYS. Date 2 never lands on a weekend.

    I think i need an IF function to -2 if Date 1 is a weekday. If date 1 is a weekend then it should only minus -1.

  54. D.R says:

    I am trying to work out from a start date of TODAY(), the days left not including workdays to a job, till the end date of the job listed in a cell. Normal procedure is to use ="end date in cell"-TODAY() but i need it to exclude workingdays if poss

  55. wayne says:

    Hello -
    I am trying to count up work days in the month everyday when the spreadsheet opens based on today. I was able to get the formula for work days in a month, and remaining work days but you cant do math on that after halfway through the month.
    =NETWORKDAYS(A1,$J$1,0) - these fields as is 1st of month and J1 is end of month field
    =NETWORKDAYS(TODAY(),EOMONTH(TODAY(),0)) - this shows me workdays left in the month...

    How can i count up work days worked in the month. For example Today is 8/13/2019, so i would need 8 days returned since that is completed work days.

    Any help would be great. thanks

  56. ISHIN says:

    in NETWORKDAY funtion if public holiday is Saturday or Sunday it still reduce the day.
    NETWORKDAY(A1, B1) return 10 day
    NETWORKDAY(A1, B1, D1:D10)
    if there is 3 public holiday between A1 to B1 then it return 7 day.
    However is the 2 day is Weekend out of 3 public holiday
    NETWORKDAY(A1, B1, D1:D10) still return 7 day instead return 9 working day.
    Because public holiday is just count how many public holiday.
    The public holiday function does not consider with weekend.

  57. Alain says:

    I could not find anything to help with this scenario:
    What should formula be if I want to add days to a date, all days getting counted (Saturdays, Sundays and holidays), but return the next work day, skipping Saturday, Sunday and holidays if the date falls on such days? Thanks in advance. Your suggestions would be highly welcome.


    I could not understood above statement, Can you please clarify.
    I need to add days from original date, final date will be come, if Saturday & Sunday date come & next working date need come. can you please suggest.

  59. Bridge says:

    Availability of a Machinery for service is between 10:00 and 18:00 every day - without any off days (weekends), excepting public holidays.
    How to calculate the hours taken between the date/time the job was logged and date/time the job was completed, excluding the 'non-available' hours (18:00 to next day 10:00)?

    This formula below works perfectly, except for excluding Sunday (option #11) as default weekly off (thereby not considering the 8 hours of availability on Sunday).
    =(NETWORKDAYS.INTL(A2,B2,11,$E2:$I2)-1)*(D2-C2)+ IF(NETWORKDAYS.INTL(B2,B2,11,$E2:$I2),MEDIAN(MOD(B2,1),C2,D2),D2)-MEDIAN(NETWORKDAYS.INTL(A2,A2,11,$E2:$I2)*MOD(A2,1),C2,D2)


    Thank you

  60. Ailton says:

    Good tutorial

  61. Hayden says:

    What if the holiday falls on a weekend? The formula doesn't count it, yet employees would be given the holiday on the following working day - so it should be counted. Thanks

  62. Faryal Shahabuddin says:

    Hello All,

    I am trying to write a formula including IF, ISBLANK and Workday.
    To fulfill these conditions:
    • Date holder received Notice
    • Date tenure publishing requirements is blank for 15 workdays.

    =IF(AND(Date_tenure_Notice>1,ISBLANK(Date_tenure_publishing_requirements>WORKDAY(Date_tenure_Notice,15,Public_Holidays))),"Requires Investigation",IF(AND(Date_tenure_Notice>1,DDate_tenure_publishing_requirements>1),"Investigated",""))

    Your help is appreciated.


  63. Rashid Khan says:

    Hello All,
    I am trying to highlight dates using 3 conditions 10, 20 and 30 days in future using Conditional Formatting.
    I have used this formula for 10 days which is not working.
    What am I missing. Any help please?
    My objective is to highlight 10, 20 and 30 days excluding Fri and Sat.
    =AND($B2>TODAY(), $B2<=WORKDAY(TODAY(),10,WEEKDAY($B2,1)<5))

    Thanks in advance.

  64. Roy Muhammad Khan says:

    Hi, at my PC in windows 7, excel sheet NETWORKDAYS is giving incorrect answers. Actually i want to manage my workdays attendance and i tried it. In the company i work with, we follow 14-by-14 duty roster therefore I put 2-April-2020 in start date cell and 15-April-2020 in end date cell, the result should be 14 but it is giving 10-January-1900. I tried many times using different methods, took also google help but all in vain and time wasting. Your help would be very helpful for me. Thanks.. Regards.. Roy Muhammad Khan

  65. maaz says:

    Pervious date:
    current Date:
    Next working date:

    Saturday is working day.
    please guide how to write formula accordingly.

  66. Faris says:

    Hello ☺
    I have a different scennario. I have monthly working schedule for many employees, i need to run a formula or highlight cells when the working days for one emplyee exceed 6 days in a row apart from the weekends becuase emplyees can work in weekends.
    Thank you in advance .

    • Hello!
      Sorry, it's not quite clear what you are trying to achieve. Weekend work is not counted? But between weekends 5 days. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you.

  67. Danielle says:

    Hi, I am trying to get a due date for each calendar day from 30 days back not counting weekends and holidays. example: January 2, 2021 would have a date of Nov 20,2020. I am confused with the workday.intl. Would i put in column A my start date and then column B my holidays and then what for the formula? (i put..this but came up with an error =workday.intl(A2,-30,0000011,A5:A8) What am i doing wrong?
    Help i am a beginner at this.

  68. Jaw says:

    Example 1
    Start date 16 NOV 2020
    End date 16 NOV 2021
    Diff in months is 13 months , i.e. 395 days

    Example 2
    Start Date 01 Dec 2020
    End Date 31 Dec 2021
    Diff is month is 12 months, i.e. 395 days

    why datedif is giving this error as there are 13 month in example1 it gives 395 days and in example2,
    12 months and also gives 395 days, how can the days be same?
    I know i am somewhere wrong but not able to trace, any help is highly appreciated.

  69. Vignesh says:


    I have a list with

    A1 : Employee ID ==== XXXX
    B1 : Shift ==== S1
    C1 : File Allotted Time Stamp ==== 1/7/2021 8:45 AM
    D1 : File Working Hours ==== 3Hrs
    E1 : Daily Shift Start ==== 10:00 AM
    F1 : Break1 Start ==== 11:15 AM
    G1 : Break1 End ==== 11:30 AM
    H1 : Lunch Start ==== 1:00 PM
    I1 : Lunch End ==== 1:30 PM
    J : Break2 Start ==== 3:00 PM
    K : Break2 End ==== 3:15 PM
    L : Daily Shift End ==== 6:00 PM
    M : Dead Line (Using Formula) ==== ????

    I need the output for deadline is 1/7/2021 10:30 AM

  70. Ed says:

    How to subtract working days using NETWORKDAYS.INTL?

    For example:

    Getting the incurred days as of today from Target Date excluding Fridays and Saturdays.

    Thanks in advance.

Post a comment

Thank you for your comment! When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to your query. We cannot guarantee that we will answer every question, but we'll do our best :)