Formula calculating dates

Question

I have a large spreadsheet and the formulas I am using are not calculating correctly 

The date in cell I1 is 4/9/2021 

the formula is shown in the snip below - 

Basically if the date in column R compared to the date in cell I1 is:

New - return "New" in column U

Not required - return "not required" 

Blank - return "Past Due"

Date is within 90 days of what is in cell I1 - return "Due within 90 Days"

Date is older than the date in cell I1 - return "Past Due"

all others not within 90 days or older than the date in cell I1 - return "Current"

It is returning everything <=90 days from within the date as "Due within 90 Days" and not past due 

so the ones that show a date of 3/16/2021 in column R are returning "Due within 90 days" when it should be returning "Past Due"

HELP

Answer 1

Isn't that what you want? You wrote

Date is older than the date in cell I1 - return "Past Due"

Answer 2

it is showing anything less than the date in I1 as past due no matter the date

Answer 3

It's a matter of the order of the arguments. Also, the syntax of DAYS is DAYS(end_date, start_date).

Try this version:

=IF(OR(R10={"New","Not Required"}),R10,IF(R10-$I$1>90,"Current",IF(R10-$I$1>=0,"Due Within 90 Days","Past Due")))

Answer 4

Change the order of your checks:

<0 "Past Due" THEN  <90 "Due within 90 days"

Why? Because if DAYS is less than 0 it is also <90, so "Due within 90 days" is capturing all of the "Past Dues"

And you don't need the Final IF - replace it with just "Current"

OR replace everything starting with the first IF(DAYS(... with this

IF(R10<$I$1,"Past Due", IF(R10-$I$1<=90,"Due with 90 days", "Current"))

Just get your closing parens balanced.

Answer 5

https://techcommunity.microsoft.com/t5/microsoft-excel/ct-p/Excel\_Cat

If I can recommend you, please also post your question in this forum.

There you can also insert a file (without sensitive data) and specialists who have specialized in Excel.

Think there you get different solutions.