# Nested IF Statements

Options
✭✭✭✭

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", ""))))))))))))

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭
Options

@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!

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!