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!

 

[markmzz]

If I understand correctly what you want, maybe this formula can helps:

In EXPECTED PAYMENT DATE column

=IF([@[PAYMENT STATUS]]="PAID","PAID",
IF([@[DATE OVERRIDE]]<>"",[@[DATE OVERRIDE]],SUM(Tab[@[PUB DATE  -6 DAYS]:[OTHER ADV DATE]]))+
6-MOD(IF([@[DATE OVERRIDE]]<>"",[@[DATE OVERRIDE]],SUM(Tab[@[PUB DATE  -6 DAYS]:[OTHER ADV DATE]])),7))

Markmzz

 

[Nicole87]

The correction formula is not bring back the nearest Friday & the text suggestion below is no good as I need it to display to format as shown

 

[Nicole87]

Thanks Markmzz but I dont think that is working? Can you write it out without the cell names for the pub date -6 to other adv date?

 

[skorpionkz]

hmmm... the formula works for me. Please check it in the file:


https://www.dropbox.com/s/jjj6xa1k2bx4pps/Payment%20Date%20Example.xlsx

 

[markmzz]

Thanks Markmzz but I dont think that is working? Can you write it out without the cell names for the pub date -6 to other adv date?

Try this (sex is fri):

Layout

Advance type	Advance type2	PUBLICATION DATE	PUB DATE -6 DAYS	DELIVERY DATE	OTHER ADV DATE	DATE OVERRIDE	EXPECTED PAYMENT DATE
Publication (PB)	PUB	01/jan/15	26/dez/14				sex 26/12/2014
Publication (PB)	PUB	01/jan/15	26/dez/14				sex 26/12/2014
Publication (HB)	PUB	02/abr/15	27/mar/15				sex 27/03/2015
Publication (HB)	PUB	03/jul/14	27/jun/14				sex 27/06/2014
Delivery	DEL		FALSO	01/jun/15			sex 05/06/2015
Publication (HB)	PUB	01/jun/15	26/mai/15				sex 29/05/2015
Delivery	DEL		FALSO	30/jun/14		27/jun/14	sex 27/06/2014
Publication (HB)	PUB	05/mar/15	27/fev/15				sex 27/02/2015
Delivery	DEL			31/mai/14		30/mai/14	sex 30/05/2014
Signature	SIG		FALSO			14/fev/14	sex 14/02/2014
Delivery	DEL		FALSO	31/mar/14		28/mar/14	sex 28/03/2014
Publication (HB)	PUB	01/jan/15	26/dez/14				sex 26/12/2014
Publication (MMP)	PUB	02/jul/15	26/jun/15				sex 26/06/2015
Signature	SIG		FALSO			14/fev/14	sex 14/02/2014
Delivery	DEL		FALSO	31/mar/16			sex 01/04/2016
Publication (HB)	PUB	07/jan/16	01/jan/16				sex 01/01/2016
Publication (MMP)	PUB	07/jul/16	01/jul/16				sex 01/07/2016
Signature	SIG		FALSO			14/fev/14	sex 14/02/2014
Delivery	DEL		FALSO	31/mar/17			sex 31/03/2017
Publication (HB)	PUB	05/jan/17	30/dez/16				sex 30/12/2016
Publication (MMP)	PUB	06/jul/17	30/jun/17				sex 30/06/2017

<tbody>
</tbody>

Formula

In EXPECTED PAYMENT DATE column (cell N9)

=IF([@[PAYMENT STATUS]]="PAID","PAID",
IF([@[DATE OVERRIDE]]<>"",[@[DATE OVERRIDE]],SUM(Tab[@[PUB DATE  -6 DAYS]:[OTHER ADV DATE]]))+
6-MOD(IF([@[DATE OVERRIDE]]<>"",[@[DATE OVERRIDE]],SUM(J9,K9,L9)),7))

Markmzz

 

[Nicole87]

That is close...although it only works for dates entered in the date override column. The others are coming up with an error #NAME?

 

[skorpionkz]

did you check formula in attached file?

 

[Nicole87]

did you check formula in attached file?

This is showing the expected payment dates as day 00-mnth-00 in all the cells still?

 

[markmzz]

That is close...although it only works for dates entered in the date override column. The others are coming up with an error #NAME?

Here all is ok.

Look at EXPECTED PAYMENT DATE column of the post #25 (this is your file with my formula).

Markmzz

 

[skorpionkz]

This is showing the expected payment dates as day 00-mnth-00 in all the cells still?

This is when you dont have "PAYED" and "@[DATE OVERRIDE]" is empty. this was my query before. In situation like this you are doing SUM of 3 cells. as in the sample there is nothing in those 3 cells. The format of the cell converts 0 to date format