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

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    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

  • Ric T
    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
    Paula Reynolds ✭✭✭
    edited 01/25/24

    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
    Toufong Vang ✭✭✭✭✭
    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))
    


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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!