Rounding due date to earliest Friday

[tati69angel]

Hello:

Any help with this would be greatly appreciated. I need help with rounding a due to date to the earliest friday. For example:

If order is received on 6/24/10 (A4) then confirmation must be sent out within ten days

A4 + 10= 7/4/10. The 4th is a saturday.

However, if the result is a saturday or sunday, it needs to be rounded to the Friday before.

I also need to subtract in the same manner.

Ie: if due date is 11/1/10 then reminder letters must be sent out 15 days before.

A4-15 = 10/17/10 which is a sunday and needs to become Friday.

Please help. thank you so much.


<!-- / message -->

 

[rallcorn]

Below formula will get what you need for the due date always being a Friday if the +10 days falls on Sat or Sunday. You should be able to figure out how formula is working & adapt for your reminder letters -- if not, let me know.

=IF(WEEKDAY(A4+10,2)<=5,A4+10,IF(WEEKDAY(A4+10,2)=6,A4+9,A4+8))

 

[xld]

Try

=A4+10-(WEEKDAY(A4+10)=7)-(WEEKDAY(A4+10)=1)*2

and

=A6-15-(WEEKDAY(A6-15)=7)-(WEEKDAY(A6-15)=1)*2

 

[tati69angel]

worked like a charm! thank you so much. now i just have to figure out how to save this formula so that i can reuse it. Thanks again!