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)
How to calculate future date say 6 months ahead in Excel

Explanation

In the example shown, the formula in C6 is

=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:   Add workdays no weekends 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:   How to calculate next anniversary date or birthday in Excel

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

Worked Example:   WORKDAY.INTL function: Description, Usage, Syntax, Examples and Explanation

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

Leave a Reply

Your email address will not be published.