If, And, Or, with Not Statement

Paula Reynolds

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?

Toufong Vang

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

Ric T

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

Paula Reynolds

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!

Toufong Vang

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

Paula Reynolds

Thank you, @Toufong Vang! This works perfectly!! I appreciate your time and providing the exact formula to copy and paste! Timesaver! :)