Categories
Excel

Determine Deadlines, Schedules, or Due Dates

One of the common tasks in Excel is to perform date and time calculations. For example, you may need to find out when a project will be completed, how long a task will take, or when a payment is due. To help you with these calculations, Excel provides two functions: WORKDAY and WORKDAY.INTL. These functions allow you to add or subtract a number of working days from a given start date, taking into account weekends and holidays. This way, you can easily determine realistic deadlines, schedules, or due dates for your work.

  1. WORKDAY Calculate the due date
    This function calculates the date that is a certain number of working days in the future from a given start date, excluding weekends and holidays.
  2. WORKDAY.INTL Calculate the due date by custom weekend days
    This function performs the same role as WORKDAY but with an additional argument allowing weekend customization.

The WORKDAY Function

If you need to calculate the number of working days between two dates in Excel, you have two options: the NETWORKDAYS function and the WORKDAY function. Both functions can exclude weekends and holidays from the calculation, but they have some differences in how they work.

The WORKDAY function returns a date (a serial number representing date) that is a specified number of working days before or after a start date. You can also optionally specify a range of cells that contain the dates of holidays, which are also skipped in the calculation.

This function is particularly useful for financial, project management, and scheduling calculations. The syntax of the WORKDAY function is:

=WORKDAY(start_date, days, [holidays])
  • start_date is the initial date from which you want to begin counting workdays.
  • days the number of workdays you want to add to the start date. Positive values will move the date forward, while negative values will move it backward in time.
  • holidays is an optional argument that specifies a range of cells that contain the dates of holidays or non-working days. These dates are excluded from the calculation.

Note: The WORKDAY function assumes a standard Monday-to-Friday workweek. If your workweek is different, consider using WORKDAY.INTL function for custom workweek calculations.

For example, if you want to calculate the date that is 10 working days after 8/1/2023, excluding weekends and the holiday on 8/15/2023, you can use this formula:

=WORKDAY("8/1/2023", 10, "8/15/2023")

The result is 45154 that represents the date 8/16/2023.

The WORKDAY function returns a serial number that represents the date. This serial number is how Excel internally represents dates.

To display this serial number as a human-readable date, right-click on the cell, choose Format Cells and select a date format from the Number tab. See the following figure:

Excluding holidays example: Suppose you want to calculate a project’s deadline which is 10 workdays away from a starting date of August 10, 2023, excluding holidays on August 14 and August 15. You would use the following formula in Excel:

=WORKDAY("2023-08-11", 10, {"2023-08-14", "2023-08-15"})

The function would return the 45167 (August 29, 2023), considering the exclusion of the two holidays. See figure.

The WORKDAY.INTL Function

The WORKDAY.INTL function is useful for calculating the end date of a project or task based on the number of working days and a custom weekend schedule. You can use this function to account for different weekend days in different countries, such as Friday-Saturday or Thursday-Friday.

Unlike the WORKDAY function, which only works for the standard Saturday-Sunday weekend, the WORKDAY.INTL function allows you to specify which days of the week are non-working days. This way, you can align your formulas with your work schedule and regional preferences.

The syntax of the function is:

=WORKDAY(start_date, days, [weekend], [holidays])
  • start_date is the initial date from which you want to begin counting workdays.
  • days the number of workdays you want to add to the start date. Positive values will move the date forward, while negative values will move it backward in time.
  • weekend is an optional argument that specifies which days of the week are considered as weekends. It can be entered as a number from 1 to 7 or 11 to 17. It also can be entered as a string of seven 0s and 1s, where 1 represents a non-working day and 0 represents a working day. The default value is 1, which means Saturday and Sunday are weekends. The table below displays the numerical and string representations of the weekend days, as follows:
NumberStringWeekend Days
1“0000011”Saturday, Sunday
2“1000001”Sunday, Monday
3“1100000”Monday, Tuesday
4“0110000”Tuesday, Wednesday
5“0011000”Wednesday, Thursday
6“0001100”Thursday, Friday
7“0000110”Friday, Saturday
11“0000001”Sunday
12“1000000”Monday
13“0100000”Tuesday
14“0010000”Wednesday
15“0001000”Thursday
16“0000100”Friday
17“0000010”Saturday
  • holidays is an optional argument that specifies a range of cells that contain the dates of holidays or non-working days. These dates are excluded from the calculation.

Similar to WORKDAY fuction, the WORKDAY.INTL function returns the serial number of the date.

For example, if you enter the following formula, it will return 45175 representing 9/6/2023:

=WORKDAY.INTL("8/25/2023",10,11)
  • 8/25/2023 the start date
  • 10 working days
  • 11 is the weekend code, representing only Sunday as the weekend day.

You can also enter a custom string of seven 0s and 1s to indicate which days are working days, where 1 means working and 0 means non-working: 

=WORKDAY.INTL("8/25/2023",10,"0000001")

The "0000001" means only Sunday is the weekend day. The above formula will return the same result 45175 (representing 9/6/2023).

You can use either 6 or "0001100" in the weekend argument to consider Thursday and Friday as weekend days. See the following formula:

=WORKDAY.INTL("8/25/2023",10,6)
"OR"
=WORKDAY.INTL("8/25/2023",10,"0001100")

You can also optionally specify a range of cells (or a date) that contains the dates of holidays, which are also skipped in the calculation. For example, to exclude weekends and the holiday on 8/30/2023, you can use this formula:

=WORKDAY.INTL("8/25/2023",10,6, "8/30/2023")
"OR"
=WORKDAY.INTL("8/25/2023",10,"0001100", "8/30/2023")

The formula returns the date that is 10 workdays after 8/25/2023, excluding weekends and holidays. The argument 6 or "0001100" means that Thursday and Friday are weekend days, and the argument “8/30/2023” refers to a holiday date. The result of the formula is 9/9/2023, which is a Saturday.

Date and Time

  1. How Date and Time Feature Works in Excel
  2. Calculate Date and Time
  3. Display Negative Time Values
  4. Count All Occurrence of a Specific Day in a Month or Two Dates
  5. Calculate Working Days and Holidays
  6. Determine Deadlines, Schedules, or Due Dates
  7. Show Total Time as Days, Hours, and Minutes
  8. Format Date and Time Values