Next business day 6 months in future

Summary

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.

In the example shown, the formula in C6 is

=WORKDAY(EDATE(B6,6)-1,1,B9:B11)

Generic formula

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

Explanation

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.

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.

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.

Author

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

This site has some of the best and most informative tutorials out there. Dave's courses are concise and to the point. They are extremely easy to learn based on his method of instruction. they are well worth the money spent.

Sam

More Testimonials

Get Training

Quick, clean, and to the point training

Learn Excel with high quality video training. Our videos are quick, clean, and to the point, so you can learn Excel in less time, and easily review key topics when needed. Each video comes with its own practice worksheet.

View Paid Training & Bundles