If you want to know what date is a certain number of days ahead or behind a date in Excel you can use simple addition and subtraction to see this.
The problem with using this method is that Excel will add or subtract the total calendar days, not taking into account any weekends or holidays.
If you need to see the working day that falls a number of working days ahead or behind a date there are two functions in Excel which can do this, WORKDAY and WORKDAY.INTL.
Download File
If you would like to follow along download the attachment below.
Default Date Calculations
To see what a future date in Excel would be you can add a certain number of days to a date and Excel will give you the adjusted future date.
If you were to use May 16th 2024, which was a Thursday, and add 15 days, Excel will show May 31st, which is fifteen days later and a Friday.
This also works if you are trying to calculate a previous date.
Ten days prior to May 16th 2024 was May 1st, which was a Wednesday.
Both these examples are giving the correct date in terms of number of days ahead or behind from the start date, but are not taking weekends or holidays into account.
WORKDAY Function
The WORKDAY function in Excel returns a date that is a specified number of working days before or after a start date. It automatically excludes weekends which by default are days that fall on a Saturday and Sunday, and you can optionally add holidays to exclude from the count as well.
=WORKDAY(start_date, days, [holidays])
start_date: The starting date from which you want to calculate.
days: The number of workdays to add or subtract. Positive numbers move forward in time, and negative numbers move backward.
holidays (optional): A list of dates to exclude in addition to weekends. This can be a range of cells containing holiday dates.
WORKDAY Without Holidays
With the same start date from the first example, May 16th 2024, and the same number of days going forwards, 15, using a WORKDAYS function Excel now returns the date June 6th 2024 instead of May 31st 2024.
The table below shows how the function treats dates compared to the default.
It is possible to use WORKDAY to count backwards as well.
To do this just include a minus sign in the days parameter.
This also exludes Saturday and Sunday and returns April 25th instead of May 1st as it would if you were to just subtract 15 days from the start date.
WORKDAY With Holidays
In the UK there was a public holiday on May 27th 2024.
To exclude this from the calculation of working days it can be included in the holiday parameter of the WORKDAY function.
Including May 27th as a holiday, the workday 15 days from May 16th is now June 7th.
The tables below shows how WORKDAY treats working days when a holiday is not included and when it is.
Holidays can also be included when calculating the number of working days prior to a start date.
In the UK May 6th 2024 was also a public holiday, when including that in the holidays parameter the function return April 24th instead of April 25th when counting 15 working days prior.
WORKDAY.INTL Function
While WORKDAY assumes weekends are always Saturday and Sunday, WORKDAY.INTL allows you to customize which days of the week are considered weekends.
The syntax is similar to WORKDAY, but adds an additional optional parameter called weekend.
=WORKDAY.INTL(start_date, days, [weekend], [holidays])
start_date: The starting date.
days: The number of working days to add or subtract.
weekend (optional): A number or string that specifies which days of the week are weekends.
holidays (optional): A list of holidays to exclude.
Weekend Options Using Numbers
Using WORKDAY.INTL allows you to choose what days you want to treat as a weekend by selecting a number from a preset list of weekend options.
The options available to treat as weekends are as follows:
If using Sunday only (number 11) as the weekend and counting fifteen work days forwards from May 16th 2024 the adjusted date would be June 3rd, instead of June 6th when WORKDAY was used and defaulted to a weekend of Saturday and Sunday.
The tables below compares WORKDAY, with Saturday and Sunday as weekends, and WORKDAY.INTL with the weekend changed to Sunday only.
Like WORKDAY you can also include a holiday if you have changed the weekend using WORKDAY.INTL.
You can also calculate backwards with WORKDAY.INTL like you can with WORKDAY by entering a minus sign before the days parameter.
Weekend Options Using Custom String
For more flexibility you can use a 7-character string to define which days are considered weekends. Each character represents a day of the week starting from Monday. A "1"
indicates that the day is a weekend, while a "0" means it is a working day.
This gives the option to treat non-consecutive days as weekends or include more or less than two days in the week as weekend.
Below are some examples of what strings you can create, although it is possible to create more than just these.
Using the custom string option if the non-consecutive days of Tuesday and Thursday are treated as weekends by entering “0101000” (the quotation marks must be included) in the weekend part of the WORKDAY.INTL function and May 27th is added in holiday then the date 15 days from May 16th would be June 7th.
Conclusion
If you want to calculate the date a number of days ahead of or before a date you can do this by adding or subtracting the number of days, but Excel will include weekends and holidays.
In order to calculate only working days ahead or behind a certain day you can use either WORKDAY or WORKDAY.INTL.
Both give the option to add holidays that you want to exclude as working days, but WORKDAY.INTL also give the option to change the weekend from the default Saturday and Sunday.
Comments