Convert text (19-NOV-2020 11:30:38 AM) into date

Stanislao Bianchini

Hello,

I am looking for a way to convert text in the following format: 19-NOV-2020 11:30:38 AM into a full date (with hour and minutes).

Any suggestions?

Thanks

Paul Newcome

HERE is a link to a post that has multiple time based solutions.

You are not going to be able to create a date/time stamp type column that directly functions as such, but you can use hidden helper columns to parse out the data and run the calculations. Feel free to browse the listed solutions and then let me know if you can't find one that works for you or if you need help adapting one of them.

Kelly Moore

Hello

Could you show us what date-time format you're needing? DD/MM/YY HR:Min AM/PM?

Stanislao Bianchini

Hello @KDM

Yes, ideally it would be great to have DD/MM/YY HR:Min AM/PM format, to subtract the date of submission of a form and have the difference in days hours and minutes.

Thanks

Kelly Moore

Without a datestamp format column type, I have always had to split my date and time into two coumns - one a DATE formatted column, and the other as TEXT/NUMBER.

There are different ways to parse the date and time into columns. Because of timezone differences, I have found that stripping the time out directly is the most reliable approach for me

To get the date into a DATE column -

=DATEONLY(Created@row)

To get the Time into a Text/Number column -

=RIGHT(Created@row, (LEN(Created@row) - 9))

As I was setting up a sheet to do what I believe is similar work to yours, I found these posts by @Paul Newcome and to be extremely helpful. I'm sure other clever friends have posted as well.

Change Time into Military time

Duration between Military time

Can you calculate Time in Smartsheet?

cheers,

Kelly

Paul Newcome

HERE is a link to a post that has multiple time based solutions.

You are not going to be able to create a date/time stamp type column that directly functions as such, but you can use hidden helper columns to parse out the data and run the calculations. Feel free to browse the listed solutions and then let me know if you can't find one that works for you or if you need help adapting one of them.

Stanislao Bianchini

Hello Paul,

Thanks for your message!

I'll browse your solutions and let you know.

Regards,

Stanislao Bianchini

Hello @Paul Newcome I'll use the following:

=DATE(VALUE(RIGHT(LEFT([Order Date in text]@row, 11), 4)), INDEX({Data Tables - Month Number}, MATCH(MID([Order Date in text]@row, 4, 3), {Data Tables - Month Name}, 0)), VALUE(LEFT(LEFT([Order Date in text]@row, 11), 2)))

to convert Order Date in text (19-NOV-2020 11:30:38 AM) into a date (19/11/2020).

For the moment I leave the time aside, the difference in days between the two dates should be sufficient.

Paul Newcome

IF you are pulling the dates and converting them into actual date values, then you should be able to subtract one date from the other to get the difference.

Stanislao Bianchini

Yes, is what I am doing and it's working fine.