In this article we explore a few different techniques for calculating the future date. These include how to exclude certain dates such as weekends and holidays.
The Simple Formula
If you just need to calculate the date a specified number of days in the future, you can simply add this number to the necessary date.
The image below shows this technique being used to add 90 days onto a start date.
Excluding Weekends from the Formula
If you want to calculate the date a specified number of work days in the future, then you need the WORKDAY function.
The image below demonstrates how to find the date in 90 work days.
Excluding Holidays with the WORKDAY Function
The WORKDAY function can also be used to exclude holiday dates from your calculation if required.
For this to work, you will first need to list the dates you want to exclude somewhere on a worksheet. This can then be referenced from within the formula like below.
Setting Custom Weekend Parameters with WORKDAY.INTL
The aforementioned WORKDAY function uses Saturday and Sunday as the weekend dates. It may be that this is not the case for you.
Fortunately the WORKDAY.INTL function was born in Excel 2010 and enables you to set custom weekend parameters.
The image below show this function providing a list of different weekend examples to pick from.
If one of these weekend parameters meets your requirements then great. However, if not you can enter a string that specifies which days of the week are working days, and which ones are non-working.
This is done by entering a 0 for a working day and a 1 for a non-working day. The image below uses a string to set the weekend as Friday, Saturday and Sunday.
No comments:
Post a Comment