Calculate project review dates
IntermediateOperations teams often schedule milestone reviews a set number of months before or after kickoff, but those reviews still need to land on a business day. This is a common case for combining EDATE and WORKDAY.
The EDATE function returns a date that is a specified number of months before or after a start date.
=EDATE(start_date, months)
The WORKDAY function returns a workday that is a specified number of business days before or after a date.
=WORKDAY(start_date, days)
In this sheet, the project kickoff date appears once in B1, each milestone's month offset appears in column C, and column D is reserved for the final review date.
Your task:
Complete D7:D15 so each row returns the date that falls the specified number of months before or after the shared kickoff date, adjusted to the next business day when needed.
Keep the kickoff date reference fixed when you fill the formula down.
Note: There's a catch with the WORKDAY function: =WORKDAY(date, 0) may not work as you expect. A workaround for this exists. Have a look at the hints if you need more guidance.
Need some help?
Hint 1
First calculate the shifted milestone date with EDATE. That gives you the calendar date before any business-day adjustment.
Hint 2
To keep weekdays unchanged but move weekend dates forward, start one day earlier than the shifted date and then add 1 workday.
Hint 3
If the first row works but copied rows break, check which reference should stay fixed. A generic nested pattern would look like =WORKDAY(EDATE($A$1, C2)-1, 1).
Related function(s)
Calculate project review dates
IntermediateOperations teams often schedule milestone reviews a set number of months before or after kickoff, but those reviews still need to land on a business day. This is a common case for combining EDATE and WORKDAY.
The EDATE function returns a date that is a specified number of months before or after a start date.
=EDATE(start_date, months)
The WORKDAY function returns a workday that is a specified number of business days before or after a date.
=WORKDAY(start_date, days)
In this sheet, the project kickoff date appears once in B1, each milestone's month offset appears in column C, and column D is reserved for the final review date.
Your task:
Complete D7:D15 so each row returns the date that falls the specified number of months before or after the shared kickoff date, adjusted to the next business day when needed.
Keep the kickoff date reference fixed when you fill the formula down.
Note: There's a catch with the WORKDAY function: =WORKDAY(date, 0) may not work as you expect. A workaround for this exists. Have a look at the hints if you need more guidance.
Need some help?
Hint 1
First calculate the shifted milestone date with EDATE. That gives you the calendar date before any business-day adjustment.
Hint 2
To keep weekdays unchanged but move weekend dates forward, start one day earlier than the shifted date and then add 1 workday.
Hint 3
If the first row works but copied rows break, check which reference should stay fixed. A generic nested pattern would look like =WORKDAY(EDATE($A$1, C2)-1, 1).