Calculate a past date based on future date

New Contributor

Hello,

I need to be able to calculate a past date based on a given date excluding weekends and holidays.
For example a package will be delivered on October 5th but for that to happen it needs to be submitted by (6 business days prior to the 5th)
How would you go about creating this?
The goal would be to keep this formula so it can be used every time a package calculation needs to be made. 

This first part would immensely help and I do not know if this next part is possible but even better would be to have a table that populates many dates based on one.

for example , a package will be delivered on October 5th. For that to happen, the package needs to be received 6 business days before. Then, a confirmation email goes out 5 business days before and a status update goes out 2 business days before that original date of the 5th.

Again, any help would be great !!

Thank you 

2 Replies

@nadmars 

 

Unless I'm mistaken you could use the Excel function WORKDAY to calculate the day 5 days prior to a given date.

I entered 10/5/22 into cell C3 (see the image below) and then this formula =WORKDAY(C3,-5) which yielded 9/28/22 as the answer.

mathetes_0-1664757661868.png

The link above will take you to a site where you can learn more, including how to incorporate holidays into the calculation;

@mathetes thank you ! I will give it a try