How to use multiple criteria when using IF, 360DAYS & today() functions in one formula???

[tori14]

Hi all, first time poster - just wondering if anyone is able to help me correct the below formula please?

What I need is a formula that will a) calculate the number of days overdue a task is from today, b) if a task is due within 60 days from today, display the message "Due", and c) if a task is due in more than 60 days from today, display the message "Not Past Due".

I've got some of it & it half works...

=IF(DAYS360(J32, TODAY())<61, "Not Past Due", DAYS360(J32, TODAY()))

The reason it half works is that is diplays "Not Past Due" when even when a value is not due more than 60 days from today.

The issue I'm having is that when I try to introduce a second IF function to display the message "Due" if the due date is within 60 days of today, the formula doesn't work. I think the problem is something to do with the fact the numbers generated are negative (because they are not yet overdue).

Can anyone figure out how to do this?

Thank you in advance

 

[Rada1]

I'm not sure that I completely understand your question, but try this:

=IF(DAYS360(J32,TODAY())<61,"Due","Not past due")

 

[tori14]

I've also got this, which is a bit closer (I think!):

=IF(DAYS360(J3,TODAY())<-61,"Not Past Due",IF(DAYS360(J3,TODAY())>-61,"Due",DAYS360(J3,TODAY())))

The issue is it doesn't give a numerical value of days overdue in the cells that are overdue, it just automatically says "Due".

 

[Rada1]

Just figured that your dates are in the future.

Try this one

=IF(ABS(DAYS360(J32,TODAY()))<61,"Due",ABS(DAYS360(J32,TODAY()))-60)

 

[tori14]

Just figured that your dates are in the future.

Try this one

=IF(ABS(DAYS360(J32,TODAY()))<61,"Due",ABS(DAYS360(J32,TODAY()))-60)

Thanks Rada1, that does work mostly - is there a way to introduce a third criteria? Some of my dates are in the past and some are in the future, and I also need cells that are due in more than 60 days to say "Not Past Due".

 

[Rada1]

Can you provide a small sample of your data with desired results, it will be easier for me to do everything correctly?

 

[barry houdini]

Unless you are specifically using DAYS360 for that reason you should note that it doesn't give an accurate count of days because (as the name suggests) it treats a year as 360 days and each month as 30 days, so from today (28 May 2015) until 1 June 2015 is 4 days but this formula:

=DAYS360(DATE(2015,5,28),DATE(2015,6,1))

....returns 3

for an accurate count of days just subtract one date from the other.

Using that approach perhaps try this formula

=IF(J3-TODAY()>60,"Not past due",IF(J3>=TODAY(),"Due in "&J3-TODAY()&" days","Overdue"))

If J3 is in the past you get "Overdue" - if J3 is today or up to 60 days in the future you get "Due in x days", if J3 is 61 days or more in the future you get "Not past due"

Does that work for you?

 

[tori14]

Thanks, that's really helpful; Accuracy is important!

I do actually need it to also give a # of days overdue, so I changed what you gave me a little:

=IF(J3-TODAY()>60,"Not past due",IF(J3>=TODAY(),"Due in "&J3-TODAY()&" days","Overdue by "&J3-TODAY()&" days"))

Now the only thing is it is giving a number value to 11 decimal places? ie: "overdue by - 701.93055555555".

Is there any way to have the result as a round number?

 

[Cosmic Wizard]

Thanks, that's really helpful; Accuracy is important!

I do actually need it to also give a # of days overdue, so I changed what you gave me a little:

=IF(J3-TODAY()>60,"Not past due",IF(J3>=TODAY(),"Due in "&J3-TODAY()&" days","Overdue by "&J3-TODAY()&" days"))

Now the only thing is it is giving a number value to 11 decimal places? ie: "overdue by - 701.93055555555".

Is there any way to have the result as a round number?

Hey I am getting "Overdue by -78 days" in a cell I tested the formula on!

The format is General, is that what your cell is formatted to??

 

[tori14]

They are all formatted to general. Could it be to do with the fact that the due date in cell J3 is being calculated by a formula?