Comments on: 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. Continue reading →

Comments page 4. Total comments: 172

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

  2. Hello,

    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.

    1. M:
      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.

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

  4. Thanks!! Worked.

  5. 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?

  6. How can I combine this in one Column.

    =IF(B5="","",WORKDAY(B5+Holidays!C1-9,2,Holidays!A2:A11-1))

    =IF(A5="","",WORKDAY(A5+Holidays!C1-2,2,Holidays!A2:A11))

  7. how can I Combine this in one sheet.

    =IF(A5="","",WORKDAY(A5+Holidays!C1-2,2,Holidays!A2:A11))
    =IF(B5="","",WORKDAY(B5+Holidays!C1-9,2,Holidays!A2:A11-1))

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

  9. Hi,
    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!

    1. Hello,

      If I understand your task correctly, please try the following formula:

      =IF(AND((NETWORKDAYS(W2,P2)-1)=0,P2>W2),1,NETWORKDAYS(W2,P2)-1)

      Hope this will help you!

  10. 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?

  11. I think I figured it out:

    =A1+IF(WEEKDAY(A1)=1,3,IF(WEEKDAY(A1)=2,3,IF(WEEKDAY(A1)=3,3,IF(WEEKDAY(A1)=4,5,IF(WEEKDAY(A1)=5,5,IF(WEEKDAY(A1)=6,5,4))))))

    Any feedback?!

  12. Hello,
    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.
    Regards,
    A

  13. 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.
    Thanks

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

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

  16. Sir/Madam,

    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

  17. 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?

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

  19. Hi,
    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.

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

    1. If you want to count all days but holidays, you can use DATE and then subtract the number of holidays.
      But if you have the list of holiday dates, the formula can be modified with COUNTIFS:
      =(DATEDIF(F1,F2,"d")-COUNTIFS(E1:E14,">="&F1,E1:E14,"<="&F2))*24
      where F1 - start date, F2 – end date, E1:E14 - holiday dates list.

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

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

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

  24. Hi
    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.

  25. 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?

    Thanks
    Bapsy

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

    =A1/(EOMONTH(TODAY(),0)-TODAY())

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

  27. i need a function to get the Actual Number of days providing Working Days
    example:
    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

  28. Hi!
    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.

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

  30. Hi,

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

  31. 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.
    E.G.
    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...

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

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

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

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

  36. =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?

  37. Help!
    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.

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

  39. dear sir / madam,

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

    i.e.
    saturday - thursday = 5

    Thursday - Wednessday = 6

    b/r
    gsr

  40. 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?

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

    Thanks!
    -jenny

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

      1. What about wanting to use that formula but excluding holidays still?

  42. Great it works. Thanks for providing with examples.

  43. Hi,

    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.

    Regards.
    Irfan Rasheed

    1. =(NETWORKDAYS(AS3,AT3,$AY$3:$AY$4)-1)*("17:30"-"8:30")+IF(NETWORKDAYS(AT3,AT3,$AY$3:$AY$4),MEDIAN(MOD(AT3,1),"17:30","8:30"),"17:30")-MEDIAN(NETWORKDAYS(AS3,AS3,$AY$3:$AY$4)*MOD(AS3,1),"17:30","8:30")

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

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

  44. 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?

    1. Hello,

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

      Hope that help ---Cheers!

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

  46. Hi,

    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.

  47. Hello,

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

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

  48. 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?

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

      1. 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. Hello, Paulo,

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

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