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.

Related formulas

Excel formula: Date is workday

Date is workday

In this example, the goal is to test a date to determine whether it is a workday. In Excel, you can use either the WORKDAY function or its more flexible sibling WORKDAY.INTL to accomplish this task. WORKDAY function The WORKDAY function calculates a date in the future or past that is, by definition...
Excel formula: Calculate retirement date

Calculate retirement date

In this example, the goal is to calculate a retirement date at age 60, based on a given birthdate. The simplest way to do this is with the EDATE function. The EDATE function will return a date n months in the future or past when given a date and the number of months to traverse. In this case, we...

Related functions

Excel WORKDAY function

WORKDAY Function

The Excel WORKDAY function returns a date in the future or past that is a given number of working days from a specified start date, excluding weekends and (optionally) holidays. You can use the WORKDAY function to calculate things like start dates, delivery dates, and completion dates...
Excel WORKDAY.INTL function

WORKDAY.INTL Function

The Excel WORKDAY.INTL function returns a date in the future or past that is a given number of working days from a specified start date, excluding weekends and (optionally) holidays. Unlike the simpler WORKDAY function, WORKDAY.INTL can be...
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

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.

Formulas

Functions

Training