Get project midpoint in Excel

by

To calculate an approximate project midpoint date, you can use the WORKDAY function.

Note: 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.

Formula

=WORKDAY(start,days/2,holidays)

Explanation

In the example shown, the formula in E5 is:

Worked Example:   How to generate random date between two dates in Excel
=WORKDAY(C5,E5/2,holidays)

where “holidays” is the named range G5:G10.

The WORKDAY is configured to get a project midpoint date by adding half of the days value to the start date:

=WORKDAY(C5,E5/2,holidays)

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.

Worked Example:   Get last working day in month in Excel

Note the WORKDAY function does not count the start date as a workday.

Custom schedule

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:

=WORKDAY.INTL(start,days/2,11,holidays)

There are many other ways to configure WORKDAY.INTL.

Worked Example:   Get project end date in Excel

Leave a Reply

Your email address will not be published. Required fields are marked *