Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

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

  • 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

  • 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!

  • 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!

Trending in Formulas and Functions

  • I have a pretty basic sheet that I am using to develop a formula, and am encountering an issue I haven't seen before. The formula isn't including my second row for some reason. It isn't a huge issue f…
    User: "jjg279"
    Answered ✓
    9
    2
  • I'm sure that this is an easy fix but I've tried a bunch of different ways and can't get this to work. I need to get the max date from a sheet to feed it into my meta data sheet (dates are stored in d…
    User: "susanmgfin"
    Answered ✓
    8
    2
  • I need help to come up with the risk formula. I tried many different formulas and tried to modify it but just can't seem to have desired results. So these are the conditions I MUST meet: Program is a …
    User: "Ronak"
    Answered ✓
    29
    6