Return a date or month

Hello! Is it possible to write a formula to get the following result -

Start Date Column: if it's a confirmed date, a date two weeks earlier in a photography due date column is calculated (this works by using this formula: (=[START DATE]@row - 14))

HOWEVER, if a start date is TBD and I put in the Start Date Column "March TBD", is there a way to have just "March TBD" reflected in the photography due date column?

I am thinking it needs to look something like this: IF(=[START DATE]@row=includes "TBD", [START DATE], [START DATE]@row-14)

Best Answer

  • KPH
    KPH Community Champion
    Answer ✓

    Hi @M_FLIGHT

    That is indeed possibe.

    Start Date and Photography Due would need to be date format columns for the -14 math to work. If so, you can use the ISDATE function to check there is a valid date in the Start Date column. That should be more reliable than looking for "TBD".

    The formula would be

    =IF(ISDATE([Start Date]@row), [Start Date]@row - 14, [Start Date]@row)

    This means if the value in the Start Date column is a date then return that date minus 14 days. If not, return the value in the Start Date column.

    Hope that helps.

Answers

  • KPH
    KPH Community Champion
    Answer ✓

    Hi @M_FLIGHT

    That is indeed possibe.

    Start Date and Photography Due would need to be date format columns for the -14 math to work. If so, you can use the ISDATE function to check there is a valid date in the Start Date column. That should be more reliable than looking for "TBD".

    The formula would be

    =IF(ISDATE([Start Date]@row), [Start Date]@row - 14, [Start Date]@row)

    This means if the value in the Start Date column is a date then return that date minus 14 days. If not, return the value in the Start Date column.

    Hope that helps.

  • Thank you, that worked!

  • KPH
    KPH Community Champion

    That's great news. Thanks for letting me know.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!