Tuesday, 17 June 2014

Calculate a Future Date in Excel

When working in Excel you may need to calculate a future date. This may be for example, when somebody’s work probation period ends, or the date a rented car should be returned.

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.

Calculate future date a specified number of days

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 weekends from formula

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.

Adding holidays to the WORKDAY function

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.

Using the WORKDAY.INTL function

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.

Setting custom weekend parameters

Watch the Video

No comments:

Post a Comment