Get start date based on target date

Get start date based on target date

Generic formula 

=WORKDAY(target,-days,holidays)

Related formulas 

Add workdays to date custom weekends

Add business days to date

Explanation

To calculate a required start date based on an established target/launch/finish date — excluding non-working days like weekends and holidays — you can use the WORKDAY function.

In the example shown, the formula in D6 is:

=WORKDAY(B6,C6,B9:B11)

Starting from an end date of Tue, 06-Jan-2015, the WORKDAY function counts backwards 10 business days (excluding weekends and holidays) and returns Thu, 18-Dec-2014. This is the required start date for an activity that requires 10 business days.

How this formula works

The WORKDAY function takes three arguments: start date, days, and (optionally) holidays. It then adds days to the date, automatically excluding weekends and holidays (if provided).

In this case, we want to count backwards from a date in the future (the launch date) by a given number of business days. The solution is to use a negative value for days.

With a negative days value, WORKDAY subtracts days from the start date, taking into account weekends and holidays.

Note that holidays are optional. The result in D5 shows the difference when holidays are not provided.

Custom weekends

If your schedule requires custom weekends (i.e. something different from Saturday and Sunday) use the WORKDAY.INTL function instead of WORKDAY.

Get start date based on target date

Generic formula 

=WORKDAY(target,-days,holidays)

Related formulas 

Add workdays to date custom weekends

Add business days to date

Explanation

To calculate a required start date based on an established target/launch/finish date — excluding non-working days like weekends and holidays — you can use the WORKDAY function.

In the example shown, the formula in D6 is:

=WORKDAY(B6,C6,B9:B11)

Starting from an end date of Tue, 06-Jan-2015, the WORKDAY function counts backwards 10 business days (excluding weekends and holidays) and returns Thu, 18-Dec-2014. This is the required start date for an activity that requires 10 business days.

How this formula works

The WORKDAY function takes three arguments: start date, days, and (optionally) holidays. It then adds days to the date, automatically excluding weekends and holidays (if provided).

In this case, we want to count backwards from a date in the future (the launch date) by a given number of business days. The solution is to use a negative value for days.

With a negative days value, WORKDAY subtracts days from the start date, taking into account weekends and holidays.

Note that holidays are optional. The result in D5 shows the difference when holidays are not provided.

Custom weekends

If your schedule requires custom weekends (i.e. something different from Saturday and Sunday) use the WORKDAY.INTL function instead of WORKDAY.