# Rounding due date to earliest Friday

#### tati69angel

##### New Member
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.

<!-- / message -->

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

#### rallcorn

##### Well-known Member
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

##### Banned
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

##### New Member
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!

1,105,975
Messages
5,508,517
Members
408,688
Latest member
Bhojraj

### This Week's Hot Topics

• Turn fraction around
Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
• TIme Clock record reformatting to ???
Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
• TextBox Match
hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
• Using Large function based on Multiple Criteria
Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
• Can you check my code please
Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
• Combining 2 pivot tables into 1 chart
Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...