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!

 

[Nicole87]

If there is nothing in Date Override & no PAID, then there will always be one date in either the Pub date-6days, Delivery date or other adv date

 

[ITWare2008]

Hi Nicole,

Until now I can't figure out when it is supposed to be the payment day. I just noticed it must be in the closest Friday... But closest to what?

I created a scenario like this:

Publishing date Expected payment date Closest Friday
(15 days after publishing)
01-05-2014 16-05-2014 16-05-2014 (because it will be a Friday)
18-02-2014 05-03-2014 (it's a Tuesday) 07-03-2014 (the very next Friday)
25-04-2014 10-05-2014 (it's a Saturday) 16-05-2014 (it will be the next Friday)

Is this ok for you?

Vândalo

 

[Nicole87]

Hi Nicole,

Until now I can't figure out when it is supposed to be the payment day. I just noticed it must be in the closest Friday... But closest to what?

I created a scenario like this:

Publishing date Expected payment date Closest Friday
(15 days after publishing)
01-05-2014 16-05-2014 16-05-2014 (because it will be a Friday)
18-02-2014 05-03-2014 (it's a Tuesday) 07-03-2014 (the very next Friday)
25-04-2014 10-05-2014 (it's a Saturday) 16-05-2014 (it will be the next Friday)

Is this ok for you?

Vândalo

It needs to be:

- If the date is already on a friday then it needs to stay on this date
- If it is not a friday then take it to the next available friday

Many Thanks.

 

[ITWare2008]

Nicole,

Let me know if I understood you.
If publishing date it's already a Friday, it will be the payment day, if not then calculate the next Friday. Is it?

Vândalo

 

[Nicole87]

Yes thanks

 

[ITWare2008]

Please try this.

=IF(WEEKDAY(AG76;16)=7;AG76;AG76+(7-WEEKDAY(AG76;16)))

where AG76 it's the publishing date

The formula will evaluate if the weekday of publishing date it's a Friday. If so it will be the payment day, otherwise will look for the next Friday.

Hope this helps.

Vândalo

 

[Nicole87]

Please try this.

=IF(WEEKDAY(AG76;16)=7;AG76;AG76+(7-WEEKDAY(AG76;16)))

where AG76 it's the publishing date

The formula will evaluate if the weekday of publishing date it's a Friday. If so it will be the payment day, otherwise will look for the next Friday.

Hope this helps.

Vândalo

That doesnt include the rest of the formula that still needs to be included...

 

[ITWare2008]

Hi again,

I'm following your post from the beggining in order to understand your question.
I notice when your payment status = "Paid", the payment day should present "Paid" in it. Right so far? Good! Otherwise, if date override has something in it it should be the value for paymente day field. Still right? If so, what is the criteria for date override?

Vândalo

 

[markmzz]

Here all is ok.

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

Markmzz

Hi Nicole87,

I'm sorry, my mistake. Try this:

=IF([@[PAYMENT STATUS]]="PAID","PAID",
IF([@[DATE OVERRIDE]]<>"",[@[DATE OVERRIDE]],[COLOR=#0000ff]SUM(J9,K9,L9)[/COLOR])+
6-MOD(IF([@[DATE OVERRIDE]]<>"",[@[DATE OVERRIDE]],SUM(J9,K9,L9)),7))

Markmzz

 

[Nicole87]

Hi Nicole87,

I'm sorry, my mistake. Try this:

=IF([@[PAYMENT STATUS]]="PAID","PAID",
IF([@[DATE OVERRIDE]]<>"",[@[DATE OVERRIDE]],[COLOR=#0000ff]SUM(J9,K9,L9)[/COLOR])+
6-MOD(IF([@[DATE OVERRIDE]]<>"",[@[DATE OVERRIDE]],SUM(J9,K9,L9)),7))

Markmzz

Excellent - that works thankyou!!

Wondering whether it is possible to add in a criteria at the start so that along with if = PAID, PAID to also have if = DO NOT PAY, DO NOT PAY & if = DUE,"DUE"?