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 2. Total comments: 172

  1. I need to calculate Complaint response time, where one office has fix sat sun off, 1 office has alternate sat sun off.
    Please help me with which formula to use and how. I tried using workday/network day and workdayintl/networkdaysintl but not getting correct result.

    Start date End date Response time (End date - Start date) but include all holidays and weekly off also.
    12-01-23 27-01-23 15
    16-01-23 23-01-23 7
    16-01-23 06-02-23 21

    1. Hi!
      Unfortunately it is not clear from your question which days you want to count. If you want to calculate the number of working days excluding Saturdays and Sundays, try the formula below. All the necessary explanations are in the article above.

      =NETWORKDAYS.INTL(A10,B10,"0000011")

      1. Hello, Ultimately I would like to create a formula for each day of each month to fill out a spreadsheet where I can use any put any start/ end date like below:

        A2= Start Date (ie 2/18/23)
        B2= End Date (ie 2/17/2024)
        ... = sequential months

        Jan- 22 | Feb-22 | Mar-22 | ... | Feb-23 | Mar-23 | Apr-23 | ... | Feb-24| ... |May-24 | Jun-24
        Sunday 0 | 0 | 0 | ... | 2 | 4 | 5 | ... | 2 | ... | 0 | 0
        Monday 0 | 0 | 0 | ... | 2 | 4 | 4 | ... | 2 | ... | 0 | 0
        Tuesday 0 | 0 | 0 | ... | 2 | 4 | 4 | ... | 2 | ... | 0 | 0
        Wednesday 0 | 0 | 0 | ... | 1 | 5 | 4 | ... | 2 | ... | 0 | 0
        Thursday 0 | 0 | 0 | ... | 1 | 5 | 4 | ... | 3 | ... | 0 | 0
        Friday 0 | 0 | 0 | ... | 1 | 5 | 4 | ... | 3 | ... | 0 | 0
        Saturday 0 | 0 | 0 | ... | 1 | 4 | 5 | ... | 3 | ... | 0 | 0

        1. Hi!
          Your request goes beyond the advice we provide on this blog. This is a complex solution that cannot be found with a single formula. If you have a specific question about the operation of a function or formula, I will try to answer it.

  2. Hello,
    I am in need of a formula which calculates the number a particular day of the week each month of the year between two dates with FY23-FY24.

    For example:
    Needing the number of Sundays (or Weds or any day) for July 2022, August 2022 all the way to June 2024. Months which do not have dates gets "0"

    A2= 2/18/2023
    B2= 2/17/2024

    I have this formula, but it doesn't allow for starting on the 18 in 2023 and ending on 17 in 2024.

    =SUMPRODUCT((TEXT(ROW(INDIRECT($A$2&":"&$B$2)),"ddddmmmm")=B$4&$A5)+0)

    $B$4 = "Sunday"
    $A$5 = "July"

    The above formula only accounts for the current year only, not 2024 and it does not take start date and end date into consideration.

    1. Hi!
      You can calculate the number of Sundays at any time using this formula. You can find all the explanations above in the article.

      =NETWORKDAYS.INTL(A2,B2,"1111110")

  3. Hi, I need a formula to calculate the date to a Wednesday two weeks prior to the current week it is in. For example, if I have 6/20/23 as a date - that is a Tuesday, but I need a formula to calculate the date to the Wednesday two weeks prior - to 6/7/23. But when I change the date to 6/23/23, that is a Friday in the same week, but I still need the calculation to show 6/7/23 (for the Wednesday two weeks prior of the existing week). But if the date moves to 6/26/23, which is the following Monday, I need it to calculate two Wednesdays prior to that week, so it should calculate to 6/14/23. Is this possible?

    Thanks in advance!

    1. Hi!
      Your dates 6/23/23 and 6/7/23 do not meet the "two weeks prior" condition. However, for your first example, I can suggest the formula
      =WORKDAY.INTL(A1-7,-1,"1101111")

  4. Hi Alexander,

    Thank you so much for this helpful guide and my apologies I am unable to translate for my need (user-error on my end)! I am attempting to calculate a date (Column E) which totals 5 workdays (M-F). If the start date (D5) begins M-F, then the 'start date' is included as 1 of the 5 days within the 5 working days and the end date (Column E) would be: =WORKDAY(D5,4)

    However, if the start date (D5) falls on a Saturday or Sunday, I need the formula for Column E to calculate =WORKDAY(D5,5). So the 5 day window would begin the following Monday-Friday as the 5 working days. For example, if the Start Date (D5) is listed as 2/10/23 which is a Friday, the desired output for (E5) is 2/16/23. If the Start Date (D6) is listed as 2/11/23, how would I write the formula to calculate that (E6) would be 2/17/23?

    Working days = Monday - Friday
    Column D = Start Date
    *If D = Monday through Friday, then Column E = D + 4 working days. If D = Saturday or Sunday, then Column E = D + 5 working days.
    Column E = Start Date + 4 Workdays

    I would so appreciate your guidance here - thank you!

    All the Best,
    Emily

    1. Hi!
      If the Start Date (D6) is 2/11/23, the formula =WORKDAY(D5,5) returns the date 2/17/23. The Start Date does not include the first working day. 2/11/23 means 2/11/23 12:00:00 AM

    1. Hello Abigail,

      This happens because though functions skip the weekend at first, you make them go one day back to that weekend by subtracting 1. I'd advise you to use the IF function to check the final day first, and then subtract the required number of days based on that.

  6. Hello!
    I have different time frames for different activities and I need to calculate for each one of them the number of working days per month (I need to apply the formula for several months).
    How can I do this?

    I know how to calculate the working days for each month, but I don't know how do I manage to calculate this for each month based in the specific time frame hat I have, also excluding holidays.

    Thank you!

    1. Hi!
      Have you tried the ways described in this blog post? Use the NETWORKDAYS function or NETWORKDAYS.INTL function. All the necessary information is in the article above.

  7. I need to Calculate Business Hours between two DateTime fields. Business hours is 9.00 A.M to 8.00 P.M

    But we work 24*7 ie. All days Including Weekends.

    How Can I Calculate business hours Between that which include Weekends also?

    Netwoekdays.intl function always exclude any one of the Weekends.

    Please anyone Give the Solution.

    01/01/2023 10.30 A.M - 01/01/2023 11.30 P.M -- Business Hours is - 9.50 or 9 hours and 30 Minutes

  8. I am trying to build a formula using IFS, OR & networkdays function however, the second string/set (starting from V21="", till end of the formula) is not giving me the desired result. Could you please help?
    =IFS(Z21="",OR(Y21=""),NETWORKDAYS(W21,X21,Holidays!$C$2:$C$24),NETWORKDAYS(Y21,Z21,Holidays!$C$2:$C$24),(V21=""),(U21=""),NETWORKDAYS(S21,T21,Holidays!$C$2:$C$24),NETWORKDAYS(U21,V21,Holidays!$C$2:$C$24)))

      1. my apologies, is there a way I can share my excel file with dummy data?
        if not, please see if this works.

        IFS(Z1="",OR(Y1=""),NETWORKDAYS(W1,X1,Holidays),NETWORKDAYS(Y1,Z1,Holidays),(V1=""),(U1=""),NETWORKDAYS(S1,T1,Holidays),NETWORKDAYS(U1,V1,Holidays)))

  9. I'm am trying to figure out a formula to track employee travel days in the last 365 days and the last 730 days. How would I word the argument so as to remove days that fall outside of either the 365 or 730 day period?

  10. I need to return the Wednesday within the same week, what function should I use.
    For example, within week 52 - start date is from 26/12/2022 to 31/12/2022, I need to return the result the 4th day of the same week, which is 28/12/2022.
    Date 4thday of same week
    1. 26/12/2022 28/12/2022
    2. 21/12/2022 28/12/2022
    3. 25/12/2022 28/12/2022
    4.28/12/2022 28/12/2022

  11. I am trying to create a start date based on the end date and how many days it will take to do a job using WORKDAYS.

    Cell A = Start Date
    Cell B = End Date
    Cell C = Days to Complete

    If I have an end date of September 30 (Cell B) and it takes 19 days to complete (Cell C), and we don't work weekends, how can I get a start date that is before the end date? Currently, it is adding the days, and I need it to subtract.

    1. Hi!
      To subtract the number of working days from the current date, use the WORKDAY.INTL function. In the days argument, use a negative value. This is described in the article above. Read carefully.

    2. I figured it out, just had to put minus sign in front of Cell C

      =WORKDAY(B8,-C8)

  12. I want to subtract 4 days from the date in a cell. if the resulting date falls on a weekend, I want it to return the first weekday before that date. I don't want it to calculate 4 days, and then add in the weekend to the calculation. I tried =workday($I$4,-4) which went over a weekend. $I$4 is 8/8/22, so the result was 8/2/22 because the span went over a weekend.

    1. Hi!
      If I understand your task correctly, the following formula should work for you:

      =IF(WEEKDAY(A1-4,2)>5,A1-4-(WEEKDAY(A1-4,2)-5),A1-4)

      Use the WEEKDAY function to determine the day of the week.

  13. I have a problem. here in the Philippines, we have regular holidays, special non-working holidays, and special working days, .. how do I insert that in the automated attendance sheet? so i can set a conditional formatting for specific holidays.. I start with =IFERROR(IF(C6="",1,MATCH(C6,RegularHoliday,0)),0).. but it will only include regular holidays.. any formula that will set all three kinds of holidays?

  14. Hello, Can I add time stamp to the below formula along with the date, for example received date+time + 2 working days and the output should be start date and time + 2 working days at the same time.

    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)

  15. I have the following in a table with the # days calculated as networking days how do I calculate the % complete?

    L M N O
    Row 11 Start Date Days End Date % Done
    Row 12 Jun 18, 2022 4 Jun 23, 2022 ???????

    1. Hi!
      I am not sure I fully understand what you mean. Explain the problem in more detail and write the expected result.

  16. I have the following in a table

    Start Date # Days End Date % Done
    Jun 18, 2022 4 Jun 23, 2022

  17. Hello, I have an odd request. I'm a RE Broker and I'm trying to have a spreadsheet that will calculate the date that the Earnest Money and Option Money need to be received by, which is 3 days. They must be receipted on a weekday, however, you can count the weekends as a day. HOWEVER, you cannot receipt the funds on a holiday or on a weekend. So if day 3 is a weekend or a holiday, it must be the next non-holiday weekday. Is there a way to figure this out?

  18. Hello. I an trying to find a formula for the following:
    U2 is the start date
    V2 is the end date, but may be blank

    If V2 is blank, count NETWORKDAYS U2 to TODAY, otherwise if V2 is not blank, count NETWORKDAYS U2 to V2

  19. I read it all, and I have some Corrections / Improvements suggestion:

    1.
    (In 'Opening Paragraph')
    Correct "as well as coming Excel 2016" - It had already come many years ago!

    2.
    (In "Excel WORKDAY.INTL function" section)

    2a.
    I don't think "Weekend string" should be highlighted in bold - it is not a name of an argument.
    I suggest highlighting in italic or underline.

    2b.
    Add (useless) Edge Cases notions:
    "0000000" - is legal "Weekend string" and acceptable
    "1111111" - is illegal "Weekend string" and unacceptable; Unless, the "Days" parameter is 0

    2c.
    (also add this info to '#VALUE! error' list, in the following section ("Excel WORKDAY and WORKDAY.INTL errors"))

    3.
    (In "Excel WORKDAY.INTL function" section)
    You had pasted the wrong sheet table picture - the same as of "Excel WORKDAY function"'s section, just above it ("excel-workday-function.png").
    Please update with the correct picture.

    Other than that,
    Thanks, once more, for all your efforts and detailed illustrated content!

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

    1. Hi,
      Pay attention to the following paragraph of the article above — NETWORKDAYS.INTL - count weekdays between 2 dates with custom weekends

  21. Hi,

    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

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

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

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

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

  25. Pervious date:
    current Date:
    Next working date:

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

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

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

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

    Regards,
    Faryal

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

    1. Hello Hayden!
      In the NETWORKDAYS function, you need to add an extra weekend to the holiday list when the holiday falls on a day off.

  30. Good tutorial

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

    WHERE:
    A2 = START DATE/TIME
    B2 = END DATE/TIME
    E2:I2 = DATES OF HOLIDAYS

    Thank you
    Bridge

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

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

  34. in NETWORKDAY funtion if public holiday is Saturday or Sunday it still reduce the day.
    example
    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.

    1. this is incorrect post.
      it work correctly

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

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

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

  38. Suhuail:
    The function TEXT used in this formula will return the day name from a date. Where the date is in J10 it looks like:
    =TEXT(J10,"dddd")

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

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

    1. 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
      =IF(A2<A1,NETWORKDAYS(A2,A1)*-1,NETWORKDAYS(A2,A1))

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

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

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

  44. Thanks!! Worked.

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

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

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

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

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

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