Excel WORKDAY Function

The WORKDAY function is one of the (Date & Time) functions of Excel. It returns the serial

number of the date before or after a specified number of workdays.

We can find this function in Date & Time of insert function Tab.

How to use WORKDAY function in excel

1. Click on empty cell (like F5 )

2. Click on fx on the below of font word (or press shift+F3)

3. In insert function tab you will see all functions

4. Select Date & Time category

5. Select WORKDAY function

6. Then select ok

7. In function arguments Tab you will see WORKDAY function

8. Start date is a serial date number that represents the start date

9. Days is the number of nonweekend and non-holiday days before or after start_date

10. Holidays is an optional array of one or more serial date numbers to exclude from the

working calendar, such as state and federal holidays and floating holidays

11. You will see the result in formula result section

Examples of WORKDAY function in excel

1. To calculate a project deadline that is due in 10 working days: =WORKDAY(TODAY(), 10)
2. To calculate the date that is 20 working days after a specific date: =WORKDAY(“5/3/2023”, 20)
3. To calculate the date that is 30 working days before a specific date: =WORKDAY(“5/3/2023”, -30)
4. To exclude weekends and holidays when counting the number of working days between two dates: =NETWORKDAYS(“4/1/2023”, “5/3/2023”)
5. To calculate the next working day after a holiday (in this example, July 4th): =WORKDAY(“7/3/2023”, 1, {“7/4/2023”})
6. To calculate the 15th working day of the current month: =WORKDAY(EOMONTH(TODAY(),-1),15)
7. To calculate the last working day of the current month: =WORKDAY(EOMONTH(TODAY(),0),-1)
8. To calculate the date that is 90 working days after a specific date, excluding non-working days: =WORKDAY.INTL(“5/3/2023”, 90, 1)
9. To include additional non-working days (such as company-specific holidays) when calculating the number of working days between two dates: =NETWORKDAYS(“4/1/2023”, “5/3/2023”,{“4/15/2023″,”4/16/2023”})
10. To calculate the last working day of a specific quarter (in this example, Q2 2023): =WORKDAY(DATE(2023,6,30),-1,{“7/4/2023”})

What is the syntax of the WORKDAY function in Excel?

The WORKDAY function in Excel is used to calculate a date that is a specified number of working days (or business days) away from a given start date. The syntax for the WORKDAY function is as follows:

=WORKDAY(start_date, days, [holidays])

where:

• start_date is the starting date for the calculation.
• days is the number of working days to add or subtract from the start_date.
• [holidays] is an optional argument that specifies additional non-working days, such as holidays. This argument is an array of dates, and can be entered as either a range of cells or an array constant.

Here are some examples of how the WORKDAY function can be used:

Example 1: To calculate a project deadline that is due in 10 working days from today:

=WORKDAY(TODAY(), 10)

Example 2: To calculate the date that is 20 working days after May 3rd, 2023:

=WORKDAY("5/3/2023", 20)

Example 3: To include Memorial Day (May 29th, 2023) when calculating the date that is 10 working days after today:

=WORKDAY(TODAY(), 10, {"5/29/2023"})

Example 4: To calculate the date that is 30 working days before June 30th, 2023:

=WORKDAY("6/30/2023", -30)

Example 5: To calculate the date that is 90 working days after January 1st, 2023, excluding weekends and holidays:

=WORKDAY.INTL("1/1/2023", 90, 1)

How does the WORKDAY function differ from the NETWORKDAYS function?

The WORKDAY and NETWORKDAYS functions in Excel are both used to calculate the number of working days between two dates, but they differ in how they handle weekends and holidays.

The WORKDAY function is used to calculate a date that is a specified number of working days away from a given start date. It allows you to exclude weekends and non-working days, such as holidays, from the calculation. The syntax for the WORKDAY function is:

=WORKDAY(start_date, days, [holidays])

where start_date is the starting date for the calculation, days is the number of working days to add or subtract from the start_date, and [holidays] is an optional argument that specifies additional non-working days.

On the other hand, the NETWORKDAYS function is used to calculate the number of working days between two dates. It counts the number of weekdays (Monday to Friday) between two dates, excluding weekends and any dates specified as non-working days. The syntax for the NETWORKDAYS function is:

=NETWORKDAYS(start_date, end_date, [holidays])

where start_date is the starting date for the calculation, end_date is the ending date for the calculation, and [holidays] is an optional argument that specifies additional non-working days.

To illustrate the difference between these two functions, consider the following examples:

Example 1: To calculate the date that is 10 working days after January 1st, 2023, excluding weekends and holidays:

=WORKDAY("1/1/2023", 10, {"1/15/2023"})

This formula will return January 15th, 2023, which is 10 working days after January 1st, excluding Martin Luther King Jr. Day on January 15th.

Example 2: To calculate the number of working days between January 1st, 2023 and January 15th, 2023, excluding weekends and holidays:

=NETWORKDAYS("1/1/2023", "1/15/2023", {"1/15/2023"})

This formula will return 9, which is the number of weekdays (Monday to Friday) between January 1st and January 15th, excluding Martin Luther King Jr. Day on January 15th.

Can the WORKDAY function be used to calculate holidays?

The WORKDAY function in Excel can be used to exclude holidays from the calculation of working days. This is done by adding the holiday dates to the third argument of the WORKDAY function, called “holidays”.

The syntax for the WORKDAY function with holidays is:

=WORKDAY(start_date, days, [holidays])

where start_date is the starting date for the calculation, days is the number of working days to add or subtract from the start_date, and [holidays] is an optional argument that specifies additional non-working days.

To use the holidays argument, you need to create a range of cells containing the holiday dates. For example, let’s say you have a list of holidays in cells A2:A6: New Year’s Day (1/1/2023), Memorial Day (5/30/2023), Independence Day (7/4/2023), Labor Day (9/4/2023), and Thanksgiving Day (11/23/2023).

You can then use this range as the third argument in the WORKDAY function, like this:

=WORKDAY("1/1/2023", 10, A2:A6)

This formula will return the date that is 10 working days after January 1st, 2023, excluding weekends and the holidays listed in cells A2:A6.

Note that the holiday dates must be entered in chronological order in the range, and they must be formatted as dates. Also, if the holidays argument is omitted, the function will only exclude weekends from the calculation.

Here’s another example to illustrate the use of the WORKDAY function with holidays:

=WORKDAY("12/24/2023", 2, {"12/25/2023";"12/31/2023";"1/1/2024"})

This formula will return December 28th, 2023, which is two working days after Christmas Eve (December 24th, 2023), excluding Christmas Day (December 25th, 2023), New Year’s Eve (December 31st, 2023), and New Year’s Day (January 1st, 2024).

How can I use the WORKDAY function to calculate a future or past date?

The WORKDAY function in Excel can be used to calculate a future or past date by adding or subtracting a specified number of working days from a given start date. This function is particularly useful when you want to exclude weekends and holidays from the calculation.

The syntax for the WORKDAY function is:

=WORKDAY(start_date, days, [holidays])

where start_date is the starting date for the calculation, days is the number of working days to add or subtract from the start_date, and [holidays] is an optional argument that specifies additional non-working days.

To calculate a future date using the WORKDAY function, you need to specify a positive value for the days argument. For example, let’s say you want to calculate the date that is 20 working days after January 1st, 2023, excluding weekends and holidays. You can use the following formula:

=WORKDAY("1/1/2023", 20, {"1/15/2023"})

This formula will return January 29th, 2023, which is 20 working days after January 1st, excluding Martin Luther King Jr. Day on January 15th.

Similarly, to calculate a past date using the WORKDAY function, you need to specify a negative value for the days argument. For example, let’s say you want to calculate the date that is 10 working days before February 14th, 2023, excluding weekends and holidays. You can use the following formula:

=WORKDAY("2/14/2023", -10, {"2/19/2023"})

This formula will return February 1st, 2023, which is 10 working days before February 14th, excluding Presidents’ Day on February 19th.

Note that the holidays argument is optional. You can use it to exclude additional non-working days from the calculation, such as public holidays or company-specific holidays.

What data types are required for the arguments of the WORKDAY function?

The WORKDAY function in Excel requires three arguments: start_date, days, and [holidays]. Each of these arguments must be a specific data type to work properly.

1. start_date: This is the starting date for the calculation, and it must be a valid Excel date or a reference to a cell containing a date value. Excel stores dates as serial numbers, where January 1st, 1900 is serial number 1, and each subsequent day is one greater than the previous day. For example, January 1st, 2023 would be stored as the serial number 44245.
2. days: This is the number of working days to add or subtract from the start_date, and it must be an integer value. Positive values will calculate a future date, while negative values will calculate a past date.
3. [holidays]: This is an optional argument that specifies additional non-working days to exclude from the calculation. It must be a range of cells containing valid Excel dates or a list of dates entered directly into the formula. The dates in the range must be entered in chronological order.

Here are some examples of the correct data types for the arguments of the WORKDAY function:

=WORKDAY("1/1/2023", 10, {"1/15/2023"})

In this example, start_date is a text string representing a date, days is an integer value, and [holidays] is a list of dates entered directly into the formula.

=WORKDAY(A2, B2, C2:C6)

In this example, start_date is a reference to a cell containing a date value, days is a reference to a cell containing an integer value, and [holidays] is a range of cells containing valid Excel dates.

Here are some examples of incorrect data types for the arguments of the WORKDAY function:

=WORKDAY("January 1st, 2023", "10", {"1/15/2023"})

In this example, start_date is a text string that does not represent a valid Excel date, and days is a text string instead of an integer value.

=WORKDAY(A2, B2, "New Year's Day")

In this example, [holidays] is a text string instead of a range of cells containing valid Excel dates.

Can the WORKDAY function be nested within other functions in Excel?

Yes, the WORKDAY function in Excel can be nested within other functions to perform more complex calculations. Nesting functions allows you to combine the results of multiple functions into a single formula.

Here’s an example of how to nest the WORKDAY function within another function:

=IF(WEEKDAY(WORKDAY("5/1/2023", 10),2)<=4,"Yes","No")

In this example, the WORKDAY function is nested within the IF and WEEKDAY functions. The formula calculates the date that is 10 working days after May 1st, 2023 using the WORKDAY function. The resulting date is then passed to the WEEKDAY function to determine the day of the week (Monday = 1, Tuesday = 2, etc.). Finally, the IF function checks if the resulting weekday is less than or equal to 4 (Wednesday). If it is, the formula returns “Yes”. Otherwise, it returns “No”.

Here’s another example of how to nest the WORKDAY function within the NETWORKDAYS function:

=NETWORKDAYS("4/1/2023", WORKDAY("4/1/2023", 10))

In this example, the WORKDAY function is used to calculate the date that is 10 working days after April 1st, 2023. This resulting date is then passed to the NETWORKDAYS function as the second argument. The NETWORKDAYS function calculates the number of working days between April 1st and the date that is 10 working days after April 1st, excluding weekends and any non-working days specified in the optional third argument.

Nesting the WORKDAY function within other functions can be useful for performing complex calculations that involve working days, weekends, and holidays. It allows you to streamline your formulas and avoid unnecessary steps in your calculations.

How can I use the WORKDAY function to exclude weekends from my calculations?

The WORKDAY function in Excel can be used to exclude weekends from your calculations by default. The function treats Saturday and Sunday as non-working days, so any dates that fall on a weekend will not be included in the calculation of working days.

To use the WORKDAY function to calculate a future or past date while excluding weekends, you simply need to specify the number of working days to add or subtract from the starting date.

Here are some examples:

=WORKDAY("5/1/2023", 10)

This formula calculates the date that is 10 working days after May 1st, 2023, excluding weekends. If any of the 10 working days fall on a weekend, such as Saturday, May 6th or Sunday, May 7th, those days will not be counted.

=WORKDAY("9/30/2023", -5)

This formula calculates the date that is 5 working days before September 30th, 2023, excluding weekends. Again, if any of the 5 working days fall on a weekend, such as Saturday, September 23rd or Sunday, September 24th, those days will not be counted.

Note that the WORKDAY function does not account for holidays by default. If you need to exclude holiday dates from your calculation, you can include them in the third argument of the function as a range of cells containing valid Excel date values.

Here’s an example of how to exclude both weekends and holidays using the WORKDAY function:

=WORKDAY("12/24/2023", 2, {"12/25/2023";"12/31/2023";"1/1/2024"})

In this formula, the calculation starts with December 24th, 2023, and adds two working days while excluding weekends and the holidays listed in the range. This formula will return the date that is two working days after Christmas Eve, while excluding Christmas Day, New Year’s Eve, and New Year’s Day.

Is it possible to include custom non-working days using the WORKDAY function?

Yes, it is possible to include custom non-working days when using the WORKDAY function in Excel. This can be useful if you need to exclude company-specific holidays or other non-working days that are not included in the default weekends and public holidays.

To include custom non-working days, you simply need to create a list of the dates to exclude and include them as a range of cells in the third argument of the WORKDAY function.

Here’s an example of how to use the WORKDAY function with custom non-working days:

=WORKDAY("1/1/2023", 5, {"1/2/2023";"1/3/2023";"1/4/2023"})

In this formula, the calculation starts with January 1st, 2023, and adds 5 working days while excluding January 2nd, 3rd, and 4th, which are custom non-working days specified in the third argument of the function.

It’s important to note that the list of non-working days must be entered in chronological order, and each date must be formatted as a valid Excel date value. You can enter the dates directly into the formula, or you can create a separate range of cells containing the dates and reference that range in the formula.

Here’s another example that includes a range of cells containing custom non-working days:

=WORKDAY("6/1/2023", 10, A1:A5)

In this formula, the calculation starts with June 1st, 2023, and adds 10 working days while excluding any dates listed in the range A1:A5. The range could contain company-specific holidays, employee vacation days, or any other non-working days that need to be excluded from the calculation.

What is the maximum number of days that can be added or subtracted using the WORKDAY function?

The maximum number of days that can be added or subtracted using the WORKDAY function in Excel is limited by the maximum value that can be represented by a signed 32-bit integer, which is 2,147,483,647.

In practical terms, this means that if you try to calculate a date that is more than approximately 5905 years in the future or past, you may encounter an error due to the limitations of Excel’s internal calculations. However, in most cases, this limit is not a concern, as it is rare to need to calculate dates more than a few decades into the future or past.

It’s important to note that when using the WORKDAY function with a large number of days, it is possible to encounter other issues such as overflow errors or problems with precision. For example, if you try to calculate a date that is very far in the future, the result may be imprecise due to rounding errors in Excel’s internal calculations.

Here are some examples of how to use the WORKDAY function with a large number of days:

=WORKDAY("1/1/2023", 1000)

This formula calculates the date that is 1000 working days after January 1st, 2023. This calculation involves a relatively large number of days, but it is well within the limits of Excel’s calculations and should return a valid result.

=WORKDAY("1/1/2030", -5000)

This formula calculates the date that is 5000 working days before January 1st, 2030. Again, this calculation involves a large number of days, but it is within the limits of Excel’s calculations and should return a valid result.

How can I troubleshoot common errors when using the WORKDAY function in Excel?

When using the WORKDAY function in Excel, you may encounter several common errors. Here are some tips for troubleshooting these errors:

1. #VALUE! error: This error occurs when one or more of the arguments in the formula is not a valid data type. To fix this error, ensure that all arguments are formatted correctly and meet the requirements for the applicable data type.
2. #NUM! error: This error occurs when the number of working days specified is negative or too large to calculate. To fix this error, ensure that the number of working days is positive and within the limits of Excel’s calculations.
3. #NAME? error: This error occurs when Excel does not recognize the name of the function. To fix this error, check that the function name is spelled correctly and that the function is available in your version of Excel.
4. #REF! error: This error occurs when the formula contains a reference to a cell that no longer exists or has been deleted. To fix this error, update the formula to reference a valid cell.
5. #NULL! error: This error occurs when the range specified for the holidays argument is not valid. To fix this error, ensure that the range contains valid dates and that the dates are sorted in chronological order.

Here are some examples of how to troubleshoot these common errors:

=WORKDAY("May 1st, 2023", 10, {"May 30th, 2023"})

In this formula, the first argument is not a valid date format, which will result in a #VALUE! error. To fix this error, change the format of the date to a valid Excel date format.

=WORKDAY("1/1/2023", -100)

In this formula, the second argument specifies a negative number of working days, which will result in a #NUM! error. To fix this error, change the number of working days to a positive value.

=WORKDAY("1/1/2023", 5000000000)

In this formula, the second argument specifies a very large number of working days, which will result in a #NUM! error. To fix this error, reduce the number of working days to within the limits of Excel’s calculations.

=WOKRDAY("1/1/2023", 10)

In this formula, the function name is misspelled as “WOKRDAY,” which will result in a #NAME? error. To fix this error, correct the spelling of the function name to “WORKDAY”.

=WORKDAY("1/1/2023", 10, A1:A5)

In this formula, the range specified for the holidays argument may not be valid, which could result in a #NULL! error. To fix this error, ensure that the range contains valid dates and that the dates are sorted in chronological order.

Related posts:

How to fix the #SPILL! error Excel DCOUNT Function Excel DCOUNTA function Excel DPRODUCT function