If, And, Or, with Not Statement
I have two different date columns ([House Hearing Date] and [Senate Hearing Date]). I need to populate a new date in another column ([Paper Due]) that is 14 days prior to the earliest date of the two dates ([House Hearing Date] and [Senate Hearing Date]). The following formula works perfectly:
=IF(AND([House Hearing Date]@row = "", [Senate Hearing Date]@row = ""), "No Data", IF(OR(ISDATE([House Hearing Date]@row), ISDATE([Senate Hearing Date]@row)), MIN([House Hearing Date]@row, [Senate Hearing Date]@row) - 14, IF(AND(ISDATE([House Hearing Date]@row), ISDATE([Senate Hearing Date]@row)), MIN([House Hearing Date]@row, [Senate Hearing Date]@row) - 14, "No Data")))
Now I need to add another piece to this formula. I need the field [Paper Due] to be blank or "n/a" if the criteria in another field [Position] is "NP."
Can anyone help?
Best Answer
-
Hi, @Paula Reynolds , if the value in [Position] overrides everything else, then wrap your formula in
IF( [Position]@row = "NP", "", (your formula) ).
Also your formula can be simplified to:
=IF(MIN([House Hearing Date]@row, [Senate Hearing Date]@row) = "0", "No Data", MIN([House Hearing Date]@row, [Senate Hearing Date]@row) - 14)
The updated formula would be:
=IF(Position@row = "NP", "", IF(MIN([House Hearing Date]@row, [Senate Hearing Date]@row) = "0", "No Data", MIN([House Hearing Date]@row, [Senate Hearing Date]@row) - 14))
Answers
-
Hi @Paula Reynolds,
Although not a formula, an automation might suit your workflow. In the automation template, select "Clear a cell value when specified criteria are met". You will be able to clear the cell value in ([Paper Due]) when [Position] is "NP".
Alternatively, use the automation "Change a cell value when specified criteria are met" to change ([Paper Due]) to "n/a" when [Position] is "NP".
To link it back, you could create the reverse automation when [Position] is is no longer "NP", to reapply your formula above.
Cheers,
Ric
-
Thanks, Ric T, for looking at this - however, that is not an option with a date column. When I make it a text/number column, my formula no longer works. Thanks anyway!
-
Hi, @Paula Reynolds , if the value in [Position] overrides everything else, then wrap your formula in
IF( [Position]@row = "NP", "", (your formula) ).
Also your formula can be simplified to:
=IF(MIN([House Hearing Date]@row, [Senate Hearing Date]@row) = "0", "No Data", MIN([House Hearing Date]@row, [Senate Hearing Date]@row) - 14)
The updated formula would be:
=IF(Position@row = "NP", "", IF(MIN([House Hearing Date]@row, [Senate Hearing Date]@row) = "0", "No Data", MIN([House Hearing Date]@row, [Senate Hearing Date]@row) - 14))
-
Thank you, @Toufong Vang! This works perfectly!! I appreciate your time and providing the exact formula to copy and paste! Timesaver! :)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!