I have dozens of sheets of data that have dates in the format of yyyymmdd_hhmmss (no slashes or colons). These came from file names. How can I convert them to mm/dd/yyyy hh:mm:ss format?
Examples:
20190524_002134 → 05/24/2019 00:21:34
20190523_235137 → 05/23/2019 23:51:37
20190524_075049 → 05/24/2019 07:50:49
Thanks for any help!
(An unsolicited suggestion.)
What you call “regular date time format” isn’t regular at all, but, concerning the date, a partly more regular (4-digit-year) variant of a “stubborn” US specific format placing the month first.
It isn’t suitable for unambiguous communication.
Use YYYY-MM-DD HH:MM:SS instead. It is basically ISO 8601 with the exception of using a simple space in place of a T probably irritating human readers.
(Only the ISO compliant formats are safely exchangeable. In addition they are understood by next to every spreadsheet in the world if conversion to numbers for calculations is needed, and they are even sortable if preserved as texts. See also xkcd: ISO 8601 )
My recommendation in short: Use the REGEX() expression given by @anon73440385, but omit the additional steps.
Hello,
assuming cell A1 contains your text string:
=VALUE(REGEX(A1;"(\d{4})(\d{2})(\d{2})_(\d{2})(\d{2})(\d{2})";"$1-$2-$3 $4:$5:$6")) (in a seaparate column)MM/DD/YYYY HH:MM:SS
Data -> Calculate -> Formula to Value
Please note: This procedure makes a real calc date + time out of your text strings (i.e. it turns it to a decimal number)
See the following sample file: Text2Date.ods
Hope that helps.
I would suggest to only use the REGEX() expression and to omit the additional steps.
Using globally standardized formats isn’t a sin against anything.
The only flaws of the originally given format are the (stubborn but excusable) usage of the underscore, and the missing delimiters facilitating human reading. The target format described by the questioner isn’t better or more regular but clearly worse.
However, REGEX() is only implemented for versions V6.2 or higher.
Thanks @anon73440385 this worked perfectly. I used all parts of the suggestion and It did exactly what I needed.
Insert a column next to the date column. Assuming the first date is in cell A5 then in cell B5 insert this formula
=VALUE(CONCATENATE(LEFT(A5,4),"-",MID(A5,5,2),"-",MID(A5,7,2)," ",MID(A5,10,2),":",MID(A5,12,2),":",MID(A5,14,2)))
You should end up with a number with several decimal places aligned right. Format the cell, right-click and select Format Cells, in the dialog box choose Time and find the format you want, OK. Copy and paste or drag cell down column B to fill other dates. Copy these new dates and paste special (CTRL+SHIFT+V) Date & Time over the top of them. You can then delete column A.
Warning. I don’t use American dates so I am only assuming Calc will recognise the concatenated number as a date, it does for UK and NZ. If not, you might have to re-arrange the order of the bits of the number. Cheers, Al