Hi:
I have these weird date and time cells that I’d like to convert to a date Numbers will recognize…
0022-02-03 00:33:50 -055036 —> MM/DD/YY
[YYYY-MM-DD HH:MM:SS -? —> MM/DD/YY]
Any ideas, please?
Thank you.
MacBook Pro 13″, OS X 10.11
If those are in column B and they all all of the same format, in another column you can use this formula to convert them:
=DATE(LEFT(B,4),MID(B,6,2),MID(B,9,2))
You said the 0022 was YYYY so you want "0022" to be the year 0022 not 2022, right? I recommend you format the results to be MM/DD/YYYY because MM/DD/YY is generally understood as 20YY or 19YY depending on the year.
If 0022 is actually 2022,
=DATE(MID(B,3,2)+2000,MID(B,6,2),MID(B,9,2))
This ignores the two leading zeros and adds 2000 to the 22.
If those are in column B and they all all of the same format, in another column you can use this formula to convert them:
=DATE(LEFT(B,4),MID(B,6,2),MID(B,9,2))
You said the 0022 was YYYY so you want "0022" to be the year 0022 not 2022, right? I recommend you format the results to be MM/DD/YYYY because MM/DD/YY is generally understood as 20YY or 19YY depending on the year.
If 0022 is actually 2022,
=DATE(MID(B,3,2)+2000,MID(B,6,2),MID(B,9,2))
This ignores the two leading zeros and adds 2000 to the 22.
Thank you so much, Badunit!
It was the second scenario you offered. (0022 is actually 2022) I sure do appreciate your thoroughness.
Happy new year!
Thanks, again. :-)
mac numbers date conversion…
