Timestamp to Date

frob

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?

Toufong Vang

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

You are my hero!