Categories

# 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 `0`s and `1`s, 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:
• `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 `0`s and `1`s 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