How to calculate future date say 6 months ahead in Excel

by

— Next business day 6 months in future

To get a date 6 months in the future, on the next work day, you can use a formula based on the WORKDAY function, with help from EDATE.

Formula

=WORKDAY(EDATE(date,6)-1,1,holidays)

Explanation

In the example shown, the formula in C6 is

Worked Example:   How to get same date next month or previous month in Excel
=WORKDAY(EDATE(B6,6)-1,1,B9:B11)

How this formula works

Working from the inside out, EDATE first calculates a date 6 months in the future. In the example shown, that date is December 24, 2015.

Worked Example:   How to get same date next year or previous year in Excel

Next, the formula subtracts 1 day to get December 23, 2015, and the result goes into the WORKDAY function as the start date, with days = 1, and the range B9:B11 provided for holidays.

Worked Example:   Add years to date in Excel

WORKDAY then calculates the next business day one day in the future, taking into account holidays and weekends.

If you need more flexibility with weekends, you can use WORKDAY.INTL.

Leave a Reply

Your email address will not be published. Required fields are marked *