Date Formatting from a Formula

Miles Allison

Hello,

I'm importing dates from an excel sheet that have a time stamp, and I have a formula that removes the time stamp in Smartsheet. This is fine except the date won't convert to 00/00/00 for that column. Does anyone know if there's a way to covert the format?

Thanks

Paul Newcome

@Marlei Try something like this:

=DATE(VALUE(MID(Timestamp@row, FIND(" ", Timestamp@row) - 4, 4)), VALUE(LEFT(Timestamp@row, FIND("/", Timestamp@row) - 1)), VALUE(MID(Timestamp@row, FIND("/", Timestamp@row) + 1, FIND("/", Timestamp@row, 4) - (FIND("/", Timestamp@row) + 1))))

Paul Newcome

How is the date displayed currently, and do you want it as mm/dd/yy or dd/mm/yy?

Marlei

@Paul Newcome Hoping to get some help on something very similar.

I am using data shuttle to import data. The data in the excel .xlsx file contains a time stamp.

Example: 3/7/2022 1:00:00 PM PST

After using a complex formula, I was able to extract the date however it comes over as 3/7/2022 and I am not able to find a way to reformat it so that SmartSheet recognizes it as a date for sorting, etc.

I would prefer the format be mm/dd/yy so that it is recognized in a date type column. Any help would be greatly appreciated.

Paul Newcome

@Marlei Try something like this:

=DATE(VALUE(MID(Timestamp@row, FIND(" ", Timestamp@row) - 4, 4)), VALUE(LEFT(Timestamp@row, FIND("/", Timestamp@row) - 1)), VALUE(MID(Timestamp@row, FIND("/", Timestamp@row) + 1, FIND("/", Timestamp@row, 4) - (FIND("/", Timestamp@row) + 1))))

Marlei

It worked! Thank you so much @Paul Newcome