Thanks:  0
Likes:  0

# Thread: Finding nearest Friday formula

1. ## Finding nearest Friday formula

Hi,

I have an IF formula that looks to a particular date and brings back the nextFriday however I need to change it so that if the date is already a Friday that is keeps this date rather than taking the following friday.

The current formula I have is:

=IFERROR(IF(WEEKDAY([@[EXPECTED PAYMENT DATE]]) < 6, [@[EXPECTED PAYMENT DATE]] + (6- WEEKDAY([@[EXPECTED PAYMENT DATE]])), [@[EXPECTED PAYMENT DATE]] + 13 - WEEKDAY([@[EXPECTED PAYMENT DATE]])),0)

Thanks!

2. ## Re: Finding nearest Friday formula

try this:
Excel 2012
AB
128/03/201428/03/2014

Sheet1

Worksheet Formulas
CellFormula
B1=IFERROR(IF(WEEKDAY(A1,15)=1,A1,A1+(8-WEEKDAY(A1,15))),0)

3. ## Re: Finding nearest Friday formula

actually there is even shorter formula:

Excel 2012
ABC
101/03/201407/03/201407/03/2014

Sheet1

Worksheet Formulas
CellFormula
B1=IFERROR(IF(WEEKDAY(A1,15)=1,A1,A1+(8-WEEKDAY(A1,15))),0)
C1=IFERROR(A1+(7-WEEKDAY(A1,16)),0)

4. ## Re: Finding nearest Friday formula

Do you need two extra cells for the second one though? I need to keep it to one cell

I think the first one works..

5. ## Re: Finding nearest Friday formula

no you dont need second cell

this are 2 different formulas which gives the same result, but the second formula is shorter and dont need IF statement.

6. ## Re: Finding nearest Friday formula

Ah ok I c.

Can you combine these two formulas into a shorter one by any chance?

=IF([@[PAYMENT STATUS]]="PAID","PAID",IF(LEN([@[DATE OVERRIDE]])>0,[@[DATE OVERRIDE]],SUM(AT97,AS97,AU97))) - looks at 3 different cells and picks up the first three unless there is a date in the fourth (date override)

&

The formulas you provided above to bring it to the nearest Friday?

Many Thanks,
Nicole

7. ## Re: Finding nearest Friday formula

1* I don't think that your formula can be changed to be shorter

2* Yes it brings nearest Friday

Code:
```=WEEKDAY(A1,16)
=IFERROR(A1+(7-WEEKDAY(A1,16)),0)```
option 16 in WEEKDAY makes week Saturday (1) - Friday (7) so if you have currently Fryday it will take todays day then + (7 - weekday of friday (7)) = 0 so it stays in same day. if today is saturday then it is Today + 6 days (7-1) and so on

8. ## Re: Finding nearest Friday formula

Can you add on the friday one to the longer formula above then?

9. ## Re: Finding nearest Friday formula

something like this?
Code:
`IF([@[PAYMENT STATUS]]="PAID","PAID",IF(LEN(IFERROR(A1+(7-WEEKDAY(A1,16)),0))>0,IFERROR(A1+(7-WEEKDAY(A1,16)),0),SUM(AT97,AS97,AU97)))`

10. ## Re: Finding nearest Friday formula

That doesnt seem to work. So I want to use the formula below and add in the rule to make the result the nearest Friday:

=IF([@[PAYMENT STATUS]]="PAID","PAID",IF(LEN([@[DATE OVERRIDE]])>0,[@[DATE OVERRIDE]],SUM(AT97,AS97,AU97))

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•