Get project midpoint
To calculate an approximate project midpoint date, you can use the WORKDAY function. In the example shown, the formula in E5 is:
where "holidays" is the named range G5:G10.
How this formula works
The WORKDAY function returns a date in the future or past, based on a start date, workdays, and optional holidays. WORKDAY automatically excludes weekends, and counts only Monday through Friday as workdays.
In the example shown, WORKDAY is configured to get a project midpoint date by adding half of the days value to the start date:
Project A is 5 workdays, so E5/2 = 2.5 days. The WORKDAY function ignores fractional values and uses only the integer portion of days, so it uses the value 2 to return a date of May 8.
Note the WORKDAY function does not count the start date as a workday.
The WORKDAY function always treats Saturday and Sunday as non-working days. To get a midpoint for a project where working days are not Monday-Friday, substitute the WORKDAY.INTL function for WORKDAY. For example, to calculate a midpoint in a project where workdays are Monday-Saturday you can use a formula like this:
There are many other ways to configure WORKDAY.INTL. This page provides details.