Hi, I have imported a csv file into numbers. One column is a date value in the format yyyymmddhhmmss. An example is 20130930092114. After the import, Numbers displays it as 2.0130E+13.
I'd like to convert that to the corresponding date & time format. I'm agreeable to using one or even two new columns (for date & time seperately, if needed). Any help is much appreciated.
iMac, OS X Mountain Lion (10.8.5)
Jon,
A formula solution to your problem is as follows:
The formula in column B is:
=DATE(MID(A, 1,4),MID(A,5,2),MID(A, 7,2))+TIMEVALUE(TIME(MID(A, 9, 2), MID(A, 11, 2), MID(A, 13, 2)))
Regards,
Jerry
Hi littlejon,
Custom Format may help.
Cell B2 contains the original format. Cell B3 is set to Custom Format in Inspector > Cells > Cell Format > Custom
As in:
Type spaces between Date and Time elements. Or type colons (:) if you wish.
I hope this helps.
Regards,
Ian.
@Jerrold Green1
It works, but the formula you posted is incorrect. The correct syntax uses semicolons between the MID functions, and so is:
DATE(MID(A;1;4);MID(A;5;2);MID(A;7;2))
I don’t know whether the TIMEVALUE portion ought to be “added” to the DATE portion using a “+” or another semicolon, since I didn’t need time conversion.
But after much wrestling with Apple’s terrible formula input interface, I finally got my ######## strings to show up as dates.
Thank you for your insight,
CP
Jerry is actually correct AND you are correct. You see, you are in a locality where you use the comma as a decimal whereas Jerry is a locality that uses the period as the decimal. Because of the locality difference the argument delimiter for functions change.
In the localities where the decimal is the period, the function delimeter is the comma
In the localities where the decimal is the comma, the function delimeter is the semicolon
Convert exponential number to date time
