Search code examples
excelif-statementformula

Excel function IF + today

I was trying to figure out what is wrong and how to change this function which seemed to be working fine last year but now it doesn't.

=IF(YEAR(D31)=2021,"Tested",IF(E31-TODAY()<0,"testing date expired",IF(E31-TODAY()<30,"testing required this month"," ")))

E31 is =D31+91 so it gives date 10-05-2021

The purpose is to show that if testing day is coming, column G should show "Testing required this month" and this should be shown but instead it shows "Tested". Any ideas what is wrong here?

enter image description here

Solution

  • A nested IF function like this will return the first true value it finds. The way this statement is written, it finds that the date being checked is in 2021 first of all, and therefore ignores the other checks (because they are in the value_if_false section of the outer IF function).
    Think of the order you want to check for these in - if a cell could meet multiple conditions, which one should it show? I'm guessing you first want to know if it's expired, then if it's required this month, then if it's in 2021 (as that is the order that the test results would overlap).

    You could either restructure your query so the nested tests are in the value_if_true section, or re-write the statement with the nesting order reversed.

    So, either like this:

    =IF(YEAR(D31)=2021,IF(E31-TODAY()<30,IF(E31-TODAY()<0, "Testing Date expired", "Testing required this month"),"Tested"),"")

    Or like this:

    =IF(E31-TODAY()<0, "Testing Date Expired", IF(E31-TODAY()<30, "Testing Required this Month", IF(Year(D31) = 2021, "Tested", "")))