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

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

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

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

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

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

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

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

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

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

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

  10. Good tutorial

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

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

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

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

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

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

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

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

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

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

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

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

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

  24. Thanks!! Worked.

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

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

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

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

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

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

  31. 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?!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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