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

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

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

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

  4. 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. I figured it out, just had to put minus sign in front of Cell C

      =WORKDAY(B8,-C8)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)