Nested IF Statements

Bethany Garcia

I'm stumped on a formula and I feel like it's probably an easy fix I'm just not seeing. I have a sheet with various date columns, and I want the formula to check to see if that date is within 180 days of today, and if it is, I want it to return Alert. However, the date isn't populated for every row, so if it's blank I want it to return "", and if the date isn't within 180 days of today I also want it to return "". I think the way I set it up it's only checking the first set of logic and my brain can't wrap around what I'm doing wrong!

=IF(ISBLANK([Home Energy Prof BPI Cert Exp - Energy Auditor]@row), "", IF([Home Energy Prof BPI Cert Exp - Energy Auditor]@row - 180 < TODAY(), "Alert", IF(ISBLANK([Home Energy Prof BPI Cert Exp - QCI]@row), "", IF([Home Energy Prof BPI Cert Exp - QCI]@row - 180 < TODAY(), "Alert", IF(ISBLANK([Lead RRP Firm License Expiration Date]@row), "", IF([Lead RRP Firm License Expiration Date]@row - 180 < TODAY(), "Alert", IF(ISBLANK([VA DPOR Resident Building Energy Analyst Exp Date]@row), "", IF([VA DPOR Resident Building Energy Analyst Exp Date]@row - 180 < TODAY(), "Alert", IF(ISBLANK([Master HVAC Expiration Date]@row), "", IF([Master HVAC Expiration Date]@row - 180 < TODAY(), "Alert", IF(ISBLANK([VA Contractor's Expiration Date]@row), "", IF([VA Contractor's Expiration Date]@row - 180 < TODAY(), "Alert", ""))))))))))))

Kelly Moore

Hey @Bethany Garcia

I'll tag-team Andrée, knowing it's late in his timezone.

Will this work for you?

=IF(OR(AND(ISDATE([Home Energy Prof BPI Cert Exp - Energy Auditor]@row), [Home Energy Prof BPI Cert Exp - Energy Auditor]@row - 180 < TODAY()), AND(ISDATE([Home Energy Prof BPI Cert Exp - QCI]@row), [Home Energy Prof BPI Cert Exp - QCI]@row - 180 < TODAY()), AND(ISDATE([Lead RRP Firm License Expiration Date]@row), [Lead RRP Firm License Expiration Date]@row - 180 < TODAY()), AND(ISDATE([VA DPOR Resident Building Energy Analyst Exp Date]@row), [VA DPOR Resident Building Energy Analyst Exp Date]@row - 180 < TODAY()), AND(ISDATE([VA Contractor's Expiration Date]@row), [VA Contractor's Expiration Date]@row - 180 < TODAY())), "Alert")

This formula will 'alert' if any of the date fields are less than 180 days from today. I noted there is nothing to say 'complete' or to stop the count. You may want to consider adding a condition to stop the count.

Kelly

Andrée Starå

Hi @Bethany Garcia

I hope you're well and safe!

Is the order in your formula correct? Does it need to look at each column one by one?

I hope that helps!

Have a fantastic weekend & Happy New Year!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Bethany Garcia

@Andrée Starå I don't really care which logic causes the Alert to display in the column, just that any of those columns being evaluated is within 180 days. The goal is to then have a report that is filtered on any row with Alert, a and the report viewer can then review the report to see which columns are out of date. We have conditional formatting set up on the sheet to highlight the columns where the date is within 180 days, but now we'd like to have a report that only shows the rows where at least one date is within 180 days.

If there were a way to filter based on conditional formatting that would meet the need, since it's already doing that check, but I haven't figured out a way to do that.

I hope that helps explain the need, please let me know if you have any suggestions!

Have a great weekend and Happy New Year to you as well!

Kelly Moore

Hey @Bethany Garcia

I'll tag-team Andrée, knowing it's late in his timezone.

Will this work for you?

=IF(OR(AND(ISDATE([Home Energy Prof BPI Cert Exp - Energy Auditor]@row), [Home Energy Prof BPI Cert Exp - Energy Auditor]@row - 180 < TODAY()), AND(ISDATE([Home Energy Prof BPI Cert Exp - QCI]@row), [Home Energy Prof BPI Cert Exp - QCI]@row - 180 < TODAY()), AND(ISDATE([Lead RRP Firm License Expiration Date]@row), [Lead RRP Firm License Expiration Date]@row - 180 < TODAY()), AND(ISDATE([VA DPOR Resident Building Energy Analyst Exp Date]@row), [VA DPOR Resident Building Energy Analyst Exp Date]@row - 180 < TODAY()), AND(ISDATE([VA Contractor's Expiration Date]@row), [VA Contractor's Expiration Date]@row - 180 < TODAY())), "Alert")

This formula will 'alert' if any of the date fields are less than 180 days from today. I noted there is nothing to say 'complete' or to stop the count. You may want to consider adding a condition to stop the count.

Kelly

Bethany Garcia

@Kelly Moore @Andrée Starå that worked, thank you so much for both of your help!

Andrée Starå

@Bethany Garcia

Excellent!

Happy to help!

✅Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful, Insightful/Vote Up/Awesome. It will make it easier for others to find a solution or help to answer!