I downloaded my call history, and the date populates as yyyymmddhhmmss
I am trying to change that into a more readable format.
I’ve tried to format the values as DD/MM/YYYY HH:MM:SS but i get a #FMT error.
Use this format code:
####"/"##"/"##" "##":"##":"##
Attention, the format is visual only, it is not possible to use it as a date.
If you want to convert to Date and Time, create two columns:
And use this formula for the Date:
=DATE(MID($A$2;1;4);MID($A$2;5;2);MID($A$2;7;2))
And this for Hora:
=TIME(MID($A$2;9;2);MID($A$2;11;2);MID($A$2;13;2))
Using @schiavinatto idea but variant.
- Format cells as
####"-"##"-"##" "##":"##":"## - Then select cells again and click Data > Text to columns, ensure that Detect special numbers is ticked. OK
- Numbers will look like
--4:54:16Format them as DateYYYY-MM-DD HH:MM:SSor as preferred
You will have proper Date and Time
because it would be a date somewhere ~55 billion years in the future 
you may convert to real dates inplace with ⇒find and replace:
search: ^(\d{4})(\d{2})(\d{2})(\d{2})(\d{2})(\d{2})$
replace:$1-$2-$3 $4:$5:$6
[x]regular Expression
1 Like
Alternatively with an auxiliary column:
=VALUE(REGEX(A2,"(\d{4})(\d{2})(\d{2})(\d{2})(\d{2})(\d{2})","$2/$3/$1 $4:$5:$6"))
If you already have split the string to the date parts, please don’t create local-format date strings out of them - use ISO date format, which would allow VALUE function to recognize "2024-08-03 18:32:40" as datetime regardless of the locale, unlike string like "08/03/2024 18:32:40", where it’s even unclear if it’s August 3rd, or March 8th.