WORKDAY function

The WORKDAY function in Excel calculates a date that is a specified number of working days before or after a given start date, excluding weekends and optionally any holidays you specify. This is useful for calculating deadlines, due dates, or project timelines that only account for business days.

Syntax

WORKDAY(start_date, days, [holidays])

Parameters

• start_date: The date from which you want to start counting the working days. This is a required parameter and can be a date entered directly or a cell reference containing a date.
• days: The number of working days to add (positive number) or subtract (negative number) from the start_date. This is a required parameter.
• [holidays] (optional): A range of one or more dates that should be considered as holidays. These dates will be excluded from the calculation of working days. The argument can be a cell reference that contains dates or a range of cells containing dates.

How It Works

• The WORKDAY function considers weekends (Saturday and Sunday by default) as non-working days.
• You can adjust the holidays argument to account for specific holidays that should be excluded from the working day calculation, such as national holidays or company-specific non-working days.
• The result of the function is a date that represents the final working day after adding or subtracting the specified number of days.

Examples

1. Basic Example (Add 10 working days): If you have a start date in cell A1 (e.g., January 1, 2025), and you want to calculate the date that is 10 working days after that, you can use:

   =WORKDAY(A1, 10)
   

   This will return a date that is 10 working days after January 1, 2025, excluding weekends (and holidays, if specified).

2. Subtracting Working Days (Subtract 5 working days): If you want to find the date 5 working days before a given date in A1:

   =WORKDAY(A1, -5)
   

   This will return a date that is 5 working days before the date in A1.

3. Including Holidays: If you have a list of holidays (e.g., January 1st and December 25th in cells B1:B2), and you want to calculate a date 10 working days after A1, excluding these holidays, you can use:

   =WORKDAY(A1, 10, B1:B2)
   

   This will return a date that is 10 working days after the start date, excluding weekends and the two holidays listed in B1:B2.

4. Using the TODAY Function: If you want to calculate the date 15 working days from today, you can combine WORKDAY with the TODAY function:

   =WORKDAY(TODAY(), 15)
   

   This will return a date that is 15 working days from the current date, excluding weekends and any holidays if provided.

Common Use Cases

• Project Management: The WORKDAY function is commonly used to calculate due dates for project tasks or deadlines, especially in business environments where weekends and holidays need to be excluded.
• Deadline Calculations: If you need to calculate when a task should be completed based on a starting date and number of working days (e.g., shipping deadlines or service-level agreements), you can use WORKDAY to ensure you get a valid business day.
• Payroll or Time Tracking: The WORKDAY function can be used in payroll systems to compute the number of workdays between two dates, excluding weekends and holidays, to calculate employee working hours or salaries.
• Custom Workweek: If your business operates on a non-standard workweek (e.g., work from Sunday to Thursday), you can use the WORKDAY.INTL function to define a custom weekend (discussed below).

Important Notes

• The WORKDAY function uses Saturday and Sunday as the default non-working days. If your business operates on a different workweek (e.g., Sunday to Thursday), you can use the WORKDAY.INTL function to specify a custom weekend.
• The holidays parameter is optional. If you have a list of holidays, you can include it to exclude those dates as well from the calculation of working days.
• The WORKDAY function does not count partial workdays. It only counts full working days.

WORKDAY.INTL Function (Custom Weekends)

If your workweek does not follow the standard Saturday/Sunday weekend, you can use the WORKDAY.INTL function to define custom weekends.

Syntax for WORKDAY.INTL

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

• weekend: A string or number that defines the weekend days. You can use a 7-character string (e.g., “0000011” for a weekend on Saturday and Sunday) or a number that represents a specific weekend type.
• [holidays] (optional): A range of one or more dates to exclude as holidays.

Example:

If your workweek is from Sunday to Thursday, and you want to find a date 10 working days from A1 (with weekends on Friday and Saturday), you can use:

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

This will return a date 10 working days after the start date, excluding Fridays and Saturdays as weekends.

Summary

The WORKDAY function in Excel is a useful tool for calculating future or past dates based on working days, excluding weekends and holidays. It is widely used for project planning, deadlines, and business operations where non-working days must be excluded from date calculations. If you need to account for a custom workweek, you can use the WORKDAY.INTL function for greater flexibility.