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]

Can you explain what exactly you want to achieve?

your formula as it is not will return "PAID" when meet criteria for Payment Status, if not then will return the date ([DATE OVERRIDE]) if this its length >0 (I suppose it checks if it is exist) if both of those criteria are not met then you adding 3 values.

So... where you want to add new rule for Day of the Week?

 

[Nicole87]

Yes so the current formula looks to the Payment Status column to see if it says PAID and either brings back a date or if not, then looks to three columns to bring back a date. So from this I then want the result to be moved to the nearest friday, is this possible to do in the same formula? I can do it in separate columns but want to do it all in one formula?

 

[skorpionkz]

try this:

=IF([@[PAYMENT STATUS]]="PAID","PAID",IF(LEN([@[DATE OVERRIDE]])>0,IFERROR(A1+(7-WEEKDAY([@[DATE OVERRIDE]],16)),0),SUM(AT97,AS97,AU97))

 

[Nicole87]

Nope..brings back Wed 04-Jul-00

 

[skorpionkz]

can you please upload small sample file so I can check what is wrong? you can use any of storage services, such as DropBox and then paste link here

 

[Nicole87]

https://www.dropbox.com/s/vtm815oglwgrzp1/Payment Date Example.xlsx

The 'Expected Payment Date' is where I am trying to put in the formula. In the first row I have entered the column you provided above (with a ' as it is missing a parenthesis so wont work). The other cells highlighted yellow show how one of the formulas just brought it to 06-Jan-00.

The 'Friday Formula' column is what im trying to achieve but in one cell.

 

[skorpionkz]

Link is not working...

 

[Nicole87]

Try this: https://dl.dropboxusercontent.com/u/96297308/Payment Date Example.xlsx

 

[skorpionkz]

Try this formula:

=IF([@[PAYMENT STATUS]]="PAID","PAID",IF(LEN([@[DATE OVERRIDE]])>0,[@[DATE OVERRIDE]]+(7-WEEKDAY([@[DATE OVERRIDE]],16)),SUM(AT96,AS96,AU96)))

Adjust the red part as required

 

[skorpionkz]

your sum might have problem there as you have date format.

i might change it last part of formula

SUM(AT96,AS96,AU96)

to

TEXT(SUM(AT96,AS96,AU96),"0.00")

this will add those 3 cells and will show result with 2 decimal places.