Extract date and Time

Katye Reed

Hello,

Is it possible to extract a date/ time from an imported excel file? It is currently reported as a string 2020112717161730.

Thanks in advance for the assist!

Katye Reed

@DMurphy Thanks so much. The first formula worked perfectly. The 2nd gave me a weird results; 1716:17:30

Dale Murphy

@Katye Reed If all you need is the date, it is easy to stitch it together from that data field:

=DATE((VALUE(LEFT([Column41]@row, 4))), (VALUE((RIGHT(LEFT([Column41]@row, 6), 2)))), (VALUE((RIGHT(LEFT([Column41]@row, 8), 2)))))

... where your string is in Column41.

This only gets you date though. I do not believe there is any way to create and hold the time (speaking practically, not existentially).

If you want to display the data formatted as time, you could do this:

=VALUE(LEFT(RIGHT([Column41]@row, 8), 4)) + ":" + VALUE(LEFT(RIGHT([Column41]@row, 4), 2)) + ":" + VALUE(RIGHT([Column41]@row, 2))

... should give you this: 2020/11/27 1716:17:30.

Cheers,

Dale

Katye Reed

@DMurphy Thanks so much. The first formula worked perfectly. The 2nd gave me a weird results; 1716:17:30