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 Excel 2010 and higher, 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 - 365. 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:

=WORKDAY(TODAY(), 30)

To subtract 30 workdays from today's date:

=WORKDAY(TODAY(), -30)

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:

=WORKDAY(A2, C2)

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

Tip. In Excel 365 and 2021, you can use WORKDAY in combination with SEQUENCE to generate a series of working days.

Excel WORKDAY.INTL function

WORKDAY.INTL is a more powerful modification of the 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)

or

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

or

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

or

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

Excel WORKDAY and WORKDAY.INTL errors

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 example

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

Excel NETWORKDAYS.INTL function

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 Excel 365 - 2010.

Using NETWORKDAYS.INTL in Excel - formula example

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)

Or

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

Table of contents

159 comments

  1. Hi, I am wanting to create a formula for the below scenario:
    We do payments every 5th day of the month (5th, 10th, 15th, 20th. 25th and 30th) but I am wanting if the date falls on a weekend to go forward/back to the weekday i.e.. if the 20th is a Sunday to go to either the 18th (Friday) or 21st (Monday).
    Thanks

  2. Hi, I was playing around with these function and came across a strange thing: let's say start date is 2023-03-01 (B1) and end date is three years later 2026-02-28 (B2) the result of Networkdays(B1;B2) is 783. Now when I use Workday(B1;783) I get 2026-03-02 which is two days later. How come?

    • Hi! These functions count dates differently. For example, the NETWORKDAY function counts the start date, while the WORKDAY function counts from the next day.

  3. I am looking for a formula where I can calculate the working days base only on start date.

    • Hi! To calculate the number of working days, you need a start date and an end date. Detailed instructions and examples are available in the article above.

  4. I want to count working hours only between two different dates but I have weird condition as I want to count "9 hours" as per day between Monday to Thursday and 4hours every Friday.
    Saturday and Sunday are non working days + calendar holidays. I'm super stressed and I did not find solution even I watch all youtube and google so much.. can someone rescue me please? If anybody need excel sheet I can send it over. Thanks in advance.

    • Hi! To calculate working days from Monday through Thursday, use the NETWORKDAYS.INTL function and Weekend String "0000111". Calculate the number of working Fridays using Weekend string "1111011". Multiply by the number of working hours.
      Please check the formula below, it should work for you:

      =NETWORKDAYS.INTL(A1,A2,"0000111")*9+NETWORKDAYS.INTL(A1,A2,"1111011")*4

  5. Hi, I'm trying to create a project management timeline for tasks. I'm using the formula =WORKDAY(F26,E26,US_Bank_Holidays)-1. F26 is the start date and E26 is the number of business days. I'm adding the -1 to the end of the formula because the start date and end date need to be inclusive. For example, a task with a start date of 04-Jan-2023 which takes 2 business days should have an end date of 05-Jan-2023 but without adding -1 excel will return the date 06-Jan-2023. My problem is that the -1 part of the formula subtracts 1 calendar day and not 1 business day (or workday), hence some of my end dates are on Sundays. Does anyone know how I can fix this so that it subtracts 1 workday instead of 1 business day?

    • Hi! Try subtracting 1 from the "days" argument of the WORKDAY function. For example, WORKDAY(F26,E26-1,US_Bank_Holidays). I hope it’ll be helpful.

      • Thank you Alexander, that worked. So simple, can't believe I didn't think of it! I'm very grateful!

  6. I am looking to create a formual that will take calculate the amount of day between dates but max each month at 20 days.

    Background, I work for a school district and it is considered the teaching year for the purpose of salary is based on on 200 days a year (September 1 to June 30). Each month is worth 20 days (regardless if it less or more). If a teacher is working 0.5 FTE from September 11, 2023 to November 30, 2023 and we count each day of the month up to a max of 20 M-F work days, we want to look at it as 15 days in September, 20 days in October (even though its 22 days) and 20 days in November (even though its 22 days). Take those 55 days, x 0.55 FTE = 30.25 days for that durations. I am stuck on making a formula that will come to this conclusion by capping the months at 20 days. Bonus if you can make it not include anything for months of July and August.

    • I think your problem cannot be solved with a single formula. Separate the working hours by months and use the NETWORKDAYS function.

  7. Hi all,

    I use this formula (=NETWORKDAYS(S2,Q2,$T$2:$T$21)) to calculate how many business days it takes to process a request without the holidays. When I try to increase the holidays, it returns a #value!. Currently, I have from the Jan 1, 2021 to the December 26, 2022 holidays that we have. If I tried to add any other holidays after that, that is when I get the #value!. Am I doing something wrong? All my dates are now in June 2023 so it should pick up the other 2023 holidays, no? Am I using the correct formula?

    Thanks for the help!

  8. I currently use a formula to count the amount of days it takes to complete a request. I would like the formula to exclude weekends in the final count of day. Any suggestions?

    Current formula which pulls from two different cells with dates inputted in the cells:

    =IF(ISBLANK(D1), "", IF(NOT(D1<H1), "1", (H1-D1+1)))

    How do I make the formula not count weekends!?

    Thanks!

  9. Hi,
    Please I have been trying to see if I can get a formular to calculate days of the week in a month.
    E.g
    1. To calculate monday to Friday in a month
    2. To calculate all the Saturdays in a month
    3. To calculate all the Sundays in a month

    Thanks

    • Hi!
      To calculate the number of certain week days in a time period, try this formula:

      =SUMPRODUCT(--(WEEKDAY(ROW(INDEX(A:A,A$1):INDEX(A:A,A$2)),2)=B1))

      where:
      A1 - start date
      A2 - end date
      B1 - day of the week (from 1 to 7), the number of which should be counted

  10. I've been trying to find a formule that when i type out a certain month, the whole date changes from the start month to the end of month. Please reply.

  11. Hi All, I am using below formula to check how many days are left from certain due date from today. How do i calculate days from a specific due date including weekends using Network days formula. Even after removing the weekend string, its still giving output by weekend exclusion in the days.

    =NETWORKDAYS(TODAY(),J3,1)
    =NETWORKDAYS.INTL(TODAY(),J6,1)

  12. hello all,

    i have a question, is the start_date tied down to a specific input date or can it do a search in a date value from a selected range of row?

    Example
    =WORKDAY(I6:K6,-5); where between range I6:K6, there is only 1 date but it can in that range.

    Thank you !

    • Hello!
      The WORKDAY function cannot search for the date you want. You must specify the address of the cell with the date, or find the date using, for example, the INDEX+MATCH functions.
      For example,

      =WORKDAY(INDEX(I6:K6,,MATCH(TRUE,I6:K6>0,0)),-5)

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

    • 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")

      • 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

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

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

    • 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")

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

    • 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")

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

    • 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

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

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

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

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

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

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

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

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

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

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

      =WORKDAY(B8,-C8)

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

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

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

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

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

  28. I have the following in a table

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  42. Good tutorial

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

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

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

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

    • this is incorrect post.
      it work correctly

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

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

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

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

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