Timestamp to Date

Options

Hey all,

I have a data shuttle where one of the variables is a time stamp. Every morning my formulas error out until I change my "Scheduled Date" properties from date to text and then back to date. It will not shuttle in if I set the properties to restrict on date. I tried writing formulas to pull out the date in my "Scheduled date only" column but then it won't recognize as a date and my miss forecast fails. I have tried; date([scheduled date]@row); left([scheduled date]@row,10) and teasing it out via year, month, and day. Any advice?


Answers

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    Options

    Hi, @frob ,

    The timestamp is being imported as text. You'll need to use the DATE() function to convert it into a date. Below is one approach.

    Syntax for DATE() is DATE ( year, month, day)

    Assuming that your timestamp is in the format "MM/DD/YYYY mm:ss"...

    year is VALUE(MID([Scheduled Date]@row,7,4))

    month is VALUE(LEFT([Scheduled Date]@row,2))

    day is VALUE(MID([Scheduled Date]@row,4,2))

    The formula in "Scheduled Date Only" would be...

    =DATE(VALUE(MID([Scheduled Date]@row,7,4)),VALUE(LEFT([Scheduled Date]@row,2)),VALUE(MID([Scheduled Date]@row,4,2)))

    The VALUE() function is required because LEFT() and MID() return text. VALUE() converts them into numbers. DATE() requires number values.

  • frob
    frob ✭✭
    Options

    You are my hero!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!