Finding nearest Friday formula

[Nicole87]

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!

 

[skorpionkz]

try this:

Excel 2012
	A	B
1	28/03/2014	28/03/2014
Sheet1

Cell Formulas
Range	Formula
B1	=IFERROR(IF(WEEKDAY(A1,15)=1,A1,A1+(8-WEEKDAY(A1,15))),0)

 

[skorpionkz]

actually there is even shorter formula:

Excel 2012
	A	B	C
1	01/03/2014	07/03/2014	07/03/2014
Sheet1

Cell Formulas
Range	Formula
B1	=IFERROR(IF(WEEKDAY(A1,15)=1,A1,A1+(8-WEEKDAY(A1,15))),0)
C1	=IFERROR(A1+(7-WEEKDAY(A1,16)),0)

 

[Nicole87]

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..

 

[skorpionkz]

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.

 

[Nicole87]

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

 

[skorpionkz]

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

2* Yes it brings nearest Friday

=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

 

[Nicole87]

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

 

[skorpionkz]

instead of what?

something like this?

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)))

 

[Nicole87]

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))