Add Workdays in Excel

To add or subtract workdays days from a date, respecting holidays but assuming a 7-day week: you can use the WORKDAY.INTL function with an argument that tells it how many days should be subtracted/added as well as which specific holidays to ignore. In our formula below C2 for this example:

=WORKDAY.INTL(A2,B2,"0000000",A7:A8)

Add workdays no weekends1

With this formula, you can have a Thursday evening deadline instead of Monday afternoon! This means that all days in the week are treated as working hours. With two holidays  in range A7:A8  and weekends set using the special syntax “0000000“, your work will be done by 5/29/2022, at midnight PST.

Explanation

WORKDAY.INTL allows you to calculate a date in the future or past that respected holidays and weekends with its special codes for designating which days are considered as such, but using masks is more flexible since it allows any day of the week to be designated into being treated like weekend ones & zeros!

You can calculate the number of days required to complete your project in an official workplace with this handy formula. Simply multiply Saturday’s total hours by 0,8 and add it onto Sunday-Friday count!

To add days excluding weekends, simply do the following:

Type this formula =WORKDAY(A2, B2) into the blank cell, and press Enter key to get your answer.

Add workdays no weekends1

Tip: To change the start date of your spreadsheet, simply enter “A2” as desired. This will allow you to input future dates into B2 for each day added on top!

We have now reached the end date, which adds 50 business days, excluding weekends.

Note: The number you’re trying to math is a 5-digit one, so in order for it to show up on your screen correctly formatted as date, click Home > Number Format > Short Date. See screenshot:

Add workdays no weekends1

Now that you have cells containing all of your holiday data, excluding weekends and any other days we want is easy.

Type this formula =WORKDAY(A2, B2, A7:A8) in blank cells and hit the enter to see the result.

Add workdays no weekends1

Tip: To create a formula that excludes holidays, use this equation. A2 is the start date, and A7:A8 are days you want to be excluded from your calculation.

Related Functions

  • Excel WORKDAY function
    The Excel WORKDAY function returns a serial number that represents a date that is the indicated number of working days before or after the starting date you specified.The syntax of the WORKDAY function is as below:=WORKDAY(start_date, days, [holidays])…
  • Excel WORKDAY.INTL function
    The Excel WORKDAY.INTL function returns the serial number of the date before or after a specified number of workdays.The syntax of the WORKDAY.INTL function is as below:= WORKDAY.INTL (start_date, days, [weekend], [holidays])…

Add the Business Days to Date

You might have come across a situation where you need to add (or subtract) the specified business days to a particular date. There is also a possibility that you might have completed this kind of task manually. Still, it seems possible if you have to add or subtract specified days to a few dates, but when it comes to a lot of dates, then doing such tasks manually would be a foolish attempt because doing this task manually would not only exhaust you but also waste a lot of time, and as a result, your work would not complete on the specified time.

But don’t worry, since after attentively reading this post, you will learn the simplest way to add business days to a list of dates in a matter of seconds.

So without any delay, let’s get into it;

_4 Add business days to date1

General Formula

In MS Excel, if you want to add days into a given date in just a matter of seconds, then  the following formula would help you out:

=WORKDAY(start date,adding_days,holidays)

Explanation of Syntax:

Before getting to know the usage of this formula for getting the work done in an efficient way, we first need to understand each syntax so that you can understand how each syntax helps to add days into a given date in just a matter of seconds :

  • WORKDAY: The WORKDAY function in MS Excel is used to compute the closest working day based on a value. Business days or workdays are any days other than Saturday and Sunday.
  • Start date: This parameter provides the input dates.
  • Holidays: This column indicates the additional holidays listed in the spreadsheet, so this formula reads this formula to decide if there is any holiday on those days.
  • Comma Symbol (,): This symbol is like a separator that aids in the separation of a list of values.
  • Parenthesis (): This primary function of this symbol is to group the elements.

Explanation:

 The formula WORKDAY function can subtract or add business days (workdays) from a date to generate a date in the past or future(respectively) that skips weekends (and holidays). Vacations are optional.

The formula in F2 in the example is:

=WORKDAY(A2,10,A12:A13)

_4 Add business days to date1

The above formula would add 10 days to dates in column A, and returns the results on column F.

Weekends are excluded by default from the WORKDAY function (Saturday and Sunday). In this example, we’ve also included a list of three holidays that occur within the date period being computed, implying that both weekends and holidays are eliminated.

Subtract Business from a Date

You can provide a negative number for days to subtract business from a date (rather than adding workdays).

For example, to obtain a date five workdays before a date in A2, use:

 =WORKDAY(A2,-5)

_4 Add business days to date1

If you find the above example challenging to grasp, don’t worry since another example below will walk you through the procedure step by step:

 Take a look at the example image below.

  1.  To begin, insert the input dates in Column A.
  2. We’ll now compute the 10 working days minus the additional holiday specified in range .
  3.  Enter the formula mentioned earlier into the formula bar section and press the ENTER:
 =WORKDAY(A2,10,A12:A13)

_4 Add business days to date1

4. Finally, the result will be shown in the selected cell.

 You can ignore the holidays range from the algorithm if you wish to compute the days without the supplied holidays. Please see the image below.

=WORKDAY(A2,10)

_4 Add business days to date1

Conclusion

 This blog post efficiently explains how you can use the basic Excel formula to add business days to a date that excludes weekends and holidays. I hope you understood it completely.

Related Functions

  • Excel WORKDAY function
    The Excel WORKDAY function returns a serial number that represents a date that is the indicated number of working days before or after the starting date you specified.The syntax of the WORKDAY function is as below:=WORKDAY(start_date, days, [holidays])…

Adding Days Exclude Specific Days of Week

As a MS Excel user, you might have come across a situation where you need to add days into the date by excluding specific days of the week. You might have done this kind of task manually, which is an acceptable way to add days into a few dates by excluding specific days. But it is an impractical way to add days to many dates and exclude specific days.

But don’t be worry about it because by reading this article carefully, you would get to know a formula to add days into different dates and exclude certain days of the week.

So let’s dive into it,

add days exclude certain days of week1

General Method:

 The formula given below will add days to date while omitting certain days (i.e., excluding Thursdays and Tuesdays and, Friday only, etc.)

=WORKDAY.INTL(Date_value,Certain_Days,Pattern_code)

Explanation of The Syntax:

 Before going into the explanation that how to get this formula into use, let’s first know about the usage of the different syntax used in this formula to understand it completely.

  •  WORKDAY.INTL: The WORKDAY.INTL function lets you personalize the weekends according to your need (any day of the week).
  • Date: This value represents the input values.
  • Days: This determines the number of days to be computed.
  • Pattern: The pattern code must be 7 digits long and contain 0s for including days and 1s for excluding days. It begins on Monday and concludes on Sunday.
  • Comma symbol (,): This symbol acts like a separator that aids in separating a list of values.
  • Parenthesis (): This primary function of this symbol is to group the elements.

Let’s See How This Formula Works:

 For example, If you got a task that requires you to add days to date while excluding specified days (i.e., excluding Thursdays and Tuesdays and, Friday alone, etc.), you may use the WORKDAY.INTL method with a custom pattern code.

The formula in F2 in the case provided is:

=WORKDAY.INTL(A2,10,"0101100")

add days exclude certain days of week1

The above formula would add the 10 days to the date in F7, except for Tuesdays,Thursdays and Fridays.

The WORKDAY.INTLfunction is based on the WORKDAY function, adding workdays to a date. WORKDAY eliminates Saturday and Sunday by default and can alternatively exclude a list of custom holidays. The WORKDAY.INTL function performs the same thing but allows you to omit any day of the week in addition to holidays.

You may either use a pre-configured code (see this page for a complete list of presets) or enter your own “pattern code” to exclude particular days of the week. The pattern code should be 7 digits long and contain either a zero or a letter for each day of the week; it must begin on Monday and end on Sunday. Days with a value of 1 are omitted, whereas days with a value of 0 are treated normally.

So, if you want to add 10 days to a date in cell A2, you may use the following formula:

For omitting Saturday and Sunday, we would write the pattern as follows:

=WORKDAY.INTL(A2,10,"0000011")

add days exclude certain days of week1

For omitting Saturday, Sunday, and Wednesday, we would write the pattern as follows:

=WORKDAY.INTL(A2,10,"0010011")

 add days exclude certain days of week1

For omitting Saturday, Sunday, Tuesday, and Thursday, we would write the pattern as follows:

=WORKDAY.INTL(A2,10,"0101011")

add days exclude certain days of week1

Related Functions

  • Excel WORKDAY function
    The Excel WORKDAY function returns a serial number that represents a date that is the indicated number of working days before or after the starting date you specified.The syntax of the WORKDAY function is as below:=WORKDAY(start_date, days, [holidays])…
  • Excel WORKDAY.INTL function
    The Excel WORKDAY.INTL function returns the serial number of the date before or after a specified number of workdays.The syntax of the WORKDAY.INTL function is as below:= WORKDAY.INTL (start_date, days, [weekend], [holidays])…

How to Add Days to Date Excluding Weekends and Holidays in Excel

This post will guide you how to add days to a given date excluding weekends and holidays with a formula in Excel.

Add Days to Date Excluding Weekends and Holidays


Assuming that you have a list of data in range B1:B4, which contain date values. And you want to add days to a given date excluding weekdays and all the specified holidays in Excel. How to do it. You can use a formula based on the WORKDAY function to achieve it. Like this:

=WORKDAY(B2,D2,C2:C3)

Cell D2 contain the number of days to be added to the date. C2:C3 is the list of holiday dates.

add days to date excluding weekday and holiday1

Type this formula into a blank cell and press Enter key. And go to HOME tab, select Short Date from the Number Format drop down list under Number group.

add days to date excluding weekday and holiday2

 

And then drag the AutoFill handle over to other cells to apply this formula.

add days to date excluding weekday and holiday3

If you only want to add days to date excluding weekdays, you can use the following formula based on WORKDAY function. Like this:

=WORKDAY(B2,D2)

add days to date excluding weekday and holiday4

Related Functions


  • Excel WORKDAY Function
    The Excel WORKDAY function returns a serial number that represents a date that is the indicated number of working days before or after the starting date you specified. The syntax of the WORKDAY function is as below:=WORKDAY(start_date, days, [holidays])…

Calculating Future Date

This post will guide you how to calculate a future date based on a given data in excel. How do I add one or more days to a given date to calculate a future date with a formula in excel. How to calculate a future date excluding weekends in excel. How to calculate a future date excluding weekends and holidays based on a given date with formula in excel.

Calculating Future Date


Assuming that you have a date list in the range of cells B1:B4, and you want to add any number of days (50) into those dates to get a new future new date. You can type this formula into a blank cell in column C.

=B2+50

calculate future date1

Then you need to press enter key in your keyboard, and drag the AutoFill Handler over other cells to apply this formula to add number 50 to get the future dates.

calculate future date2

Calculating Future Date Excluding Weekends


If you want to calculate a new future date excluding weekends, and you need to use the WORKDAY function to create a new formula to exclude all weekends in the future date. So you can write down this formula:

=WORKDAY(B2,50)

#1 Type this formula into the formula box of the cell C2, and then press Enter key in your keyboard.

calculate future date3

#2 go to HOME tab, click Short Date from the General drop down menu list.

calculate future date5

Calculating Future Date Excluding Weekends and Holidays


If you want to calculate a future date excluding weekends and holidays based on a given date, you still need to use the WORKDAY function to create a new formula. At this time, you need to specify which dates are holidays in the certain range of cells (such as: E1:E3).

You can use the following formula to achieve the result:

=WORKDAY(B2,50,$E$1:$E$3)

calculate future date4

Then you can go to HOME tab, click Short Date from the General drop down menu list.so the date can be converted as a short date from a five-digit number.

Related Functions


  • Excel WORKDAY Function
    The Excel WORKDAY function returns a serial number that represents a date that is the indicated number of working days before or after the starting date you specified. The syntax of the WORKDAY function is as below:=WORKDAY(start_date, days, [holidays])…

Excel WORKDAY.INTL Function

This post will guide you how to use Excel WORKDAY.INTL function with syntax and examples in Microsoft excel.

Description

The Excel WORKDAY.INTL function returns the serial number of the date before or after a specified number of workdays. And this function allow you to exclude weekends and holidays as non-working days. You can use the WORKDAY.INTL function to calculate working days excluding weekdays and holidays.

The WORKDAY.INTL function is a build-in function in Microsoft Excel and it is categorized as a DATE and TIME Function.

The WORKDAY.INTL function is available in Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000, Excel 2011 for Mac.

Syntax

The syntax of the WORKDAY.INTL function is as below:

= WORKDAY.INTL (start_date, days, [weekend], [holidays])

Where the WORKDAY.INTL function arguments is:

  • Start_date –This is a required argument. The starting date from which you want to count the number of working days. The date should be typed as a valid Excel time a serial date.
  • days – This is a required argument. The number of working days that you want to add. A positive value for days yields a future date; a negative value yields a past date.
  • Weekend – This is an optional argument. Determine which days of the week that are weekend days and are not considered working days. Weekend is a weekend number and it can be one of the following numbers.
weekend-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
  • holidays – This is an optional argument. The list of holidays that you want to exclude from the working days. It can be a range of cells that contain the holiday dates or it can be a list of serial numbers that represent the holiday dates.

Note:

  • If any argument is not a valid Excel date, a #VALUE! Error is returned.
  • If start date value plus days is an invalid date, the WORKDAY function returns #NUM! Error.
  • A serial date is how the Microsoft Excel stores dates and it represents the number of days since 1900-01-01, so the January 1, 1900 date is serial number 1 by default.
  • If days value is not a integer number, it will be truncated.
  • If a weekend string is of invalid length or contains invalid characters, WORKDAY.INTL returns the #VALUE! Error value.

Excel WORKDAY.INTL Function Examples

The below examples will show you how to use Excel WORKDAY.INTL Function to return the working days from the start date.

1# get the date 50 workdays from the starting date “3/20/2018”, counting Sunday and Monday as a weekend day, so the weekend argument should be 2, enter the following formula in Cell B1.

=WORKDAY.INTL(DATE(2018,3,20),50,2)

excel workdayintl examples1

2# get the date 50 workdays from the starting date “3/20/2018”, excluding holidays 5/1/2018, and counting only Sundays as weekend day, the weekend argument should be 11, type the following formula in Cell B2.

=WORKDAY(DATE(2018,3,20),50,11,DATE(2018,5,1))

excel workdayintl examples2


Related Functions

  • Excel WORKDAY function
    The Excel WORKDAY function returns a serial number that represents a date that is the indicated number of working days before or after the starting date you specified.The syntax of the WORKDAY function is as below:=WORKDAY(start_date, days, [holidays])…
  • Excel WEEKDAY function
    The Excel WEEKDAY function returns a integer value representing the day fo the week for a given Excel date and the value is range from 1 to 7.The syntax of the WEEKDAY function is as below:=WEEKDAY (serial_number,[return_type])…
  • Excel DAY function
    The Excel DAY function returns a day of a date (from 1 to 31).The DAY function is a build-in function in Microsoft Excel and it is categorized as a DATE and TIME Function.The syntax of the DAY function is as below:= DAY (date_value)…
  • Excel DATE function
    The Excel DATE function returns the serial number for a date.The syntax of the DATE function is as below:= DATE (year, month, day)…

Excel WORKDAY Function

This post will guide you how to use Excel WORKDAY function with syntax and examples in Microsoft excel.

Description

The Excel WORKDAY function returns a serial number that represents a date that is the indicated number of working days before or after the starting date you specified. So you can add a specified number of working days to the starting date and then returns a serial date. The working days will exclude weekends and any dates specified as holidays.

You can use the WORKDAY function to calculate working days and non-working days.

The WORKDAY function is a build-in function in Microsoft Excel and it is categorized as a DATE and TIME Function.

The WORKDAY function is available in Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000, Excel 2011 for Mac.

Syntax

The syntax of the WORKDAY function is as below:

=WORKDAY(start_date, days, [holidays])

Where the WORKDAY function arguments is:

  • Start_date –This is a required argument. The starting date from which you want to count the number of working days. The date should be typed as a valid Excel time a serial date.
  • days – This is a required argument. The number of working days that you want to add. A positive value for days yields a future date; a negative value yields a past date.
  • holidays – This is an optional argument. The list of holidays that you want to exclude from the working days. It can be a range of cells that contain the holiday dates or it can be a list of serial numbers that represent the holiday dates.

Note:

  • If any argument is not a valid Excel date, a #VALUE! Error is returned.
  • If start date value plus days is an invalid date, the WORKDAY function returns #NUM! Error.
  • A serial date is how the Microsoft Excel stores dates and it represents the number of days since 1900-01-01, so the January 1, 1900 date is serial number 1 by default.
  • If days value is not a integer number, it will be truncated.

Excel WORKDAY Function Examples

The below examples will show you how to use Excel WORKDAY Function to return the working days from the start date.

1# get the date 50 workdays from the starting date “3/20/2018”, enter the following formula in Cell B1.

=WORKDAY(DATE(2018,3,20),50)

excel workday examples1

2# get the date 50 workdays from the starting date “3/20/2018”, excluding holidays 5/1/2018, type the following formula in Cell B2.

=WORKDAY(DATE(2018,3,20),50,DATE(2018,5,1))

excel workday examples2

Related Functions


  • Excel WEEKDAY function
    The Excel WEEKDAY function returns a integer value representing the day fo the week for a given Excel date and the value is range from 1 to 7.The syntax of the WEEKDAY function is as below:=WEEKDAY (serial_number,[return_type])…
  • Excel DAY function
    The Excel DAY function returns a day of a date (from 1 to 31).The DAY function is a build-in function in Microsoft Excel and it is categorized as a DATE and TIME Function.The syntax of the DAY function is as below:= DAY (date_value)…
  • Excel DATE function
    The Excel DATE function returns the serial number for a date.The syntax of the DATE function is as below:= DATE (year, month, day)…

More Excel Workday Function Examples


  • Calculating Future Date
    If you want to calculate a new future date excluding weekends, and you need to use the WORKDAY function to create a new formula to exclude all weekends in the future date.…