So close... help please - IF AND NOT ISBLANK two columns to a results column

Options

Hello Community!

I am close on this but I just cannot figure out this last piece and it is driving me nuts. I have two date columns and depending which columns have values I need to record a value in a third column from one of those columns. I have it working with a one column scenario either way but I cannot get the AND function to work. I think I have been staring at this too long and I am lost. Can you help figure out what I need to piece these together?

Here is a screenshot of my sheet and what I am trying to accomplish. The first four rows are the results I am trying to accomplish, the ones in yellow are the pieces I have been able to partially make work. Formula text below


=IF(NOT(ISBLANK([Guidance Date]@row)), [Guidance Date]@row, "") - Works for one column only.

=IF(NOT(ISBLANK([Client ETA]@row)), [Client ETA]@row, "") - works for one column only.

=IF(ISBLANK([Guidance Date]@row), "", IF(ISBLANK([Client ETA]@row), "TBD", [Client ETA]@row)) - works for one column only

=IF(NOT(AND(ISBLANK([Guidance Date]@row), ISBLANK([Client ETA]@row), [Client ETA]@row, ""))) - Incorrect argument and I am stumped after staring at this for too long.

Thank you in advance for any advice you may have!

Best Answer

  • Mary Royston
    Mary Royston ✭✭✭
    Answer ✓
    Options

    Hey @Bryan Moss,

    So if I am understanding correctly here is a formula you can use with a screenshot of how it works below:

    =IF(AND(ISBLANK([Client ETA]@row), ISBLANK([Guidance Date]@row)), "TBD", IF(ISBLANK([Client ETA]@row), [Guidance Date]@row, [Client ETA]@row))

    If both are blank, it is TBD.

    If Guidance and Client have a date it will put the Client Date

    If only one date is put, it will display that date.

    Hope that helps, let me know if that works :)

Answers

  • Mary Royston
    Mary Royston ✭✭✭
    Answer ✓
    Options

    Hey @Bryan Moss,

    So if I am understanding correctly here is a formula you can use with a screenshot of how it works below:

    =IF(AND(ISBLANK([Client ETA]@row), ISBLANK([Guidance Date]@row)), "TBD", IF(ISBLANK([Client ETA]@row), [Guidance Date]@row, [Client ETA]@row))

    If both are blank, it is TBD.

    If Guidance and Client have a date it will put the Client Date

    If only one date is put, it will display that date.

    Hope that helps, let me know if that works :)

  • Bryan Moss
    Bryan Moss ✭✭✭✭
    Options

    Thank you @Mary Royston !!! I think I was thinking backwards and making it too hard. Really appreciate your help!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!