A Formula to Back Calculate Dates using ONLY Weekdays

Hello,

I am trying to create a formula to back calculate a date using only weekdays.

This formula needs to be 10 days prior to a specific date only calculating with weekdays.

Example: Target date is 10/5. I need a formula that will give me the date of 9/25.

In addition I also need a formula that is then 3 days prior to the date that was calculated from this first formula, again only calculating with weekdays.

Example: Since the date required from the previous formula is 9/25, I need a formula that would give me a date of 9/20 from the 9/25.

Please help!

Thank you

=a1-sumproduct(row($a$1:index($a:$a,b1*2))*(networkdays(a1-row($a$1:index($a:$a,b1*2)),a1)=b1))

Originally Posted by OfficeJob1897

... This formula needs to be 10 days prior to a specific date only calculating with weekdays.
Example: Target date is 10/5. I need a formula that will give me the date of 9/25...

Could you please clarify how you arrived at 9/25?

10/5, 10/4, 10/3, 10/2, 9/29, 9/28, 9/27, 9/26, 9/25 -- that's 9 days.

I apologize. A week prior to 10/5 is 9/28. Minus 3 business days.

What are A and B supposed to reference? Thank you.

Originally Posted by OfficeJob1897

I apologize. A week prior to 10/5 is 9/28. Minus 3 business days.

Still not clear.

Is this what you are after?

Cell A1: 10/5/2017
Cell B1: =WORKDAY(A1-7,-3) returns 9/25/2017
Cell C1: =WORKDAY(B1,-3) returns 9/20/2017

this is why uploading a sample workbook makes your and our lives easier, and eliminates these conversations

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

YES thank you so much!!