Apple Event: May 7th at 7 am PT
I imported a csv file and the date column is in text format. How do I convert it from m/d/yyyy text format to yyyy-mm-dd DATE format in Numbers v 3.1?
Thanks for any help you can give!
OS X Mavericks (10.9.2), Numbers v 3.1
select the column then format using the cell formatter:
Nope, that doesn't work because it doesn't recognize the slashes. I figured it out. I added a new column (formatted as a date) and put this formula in it: =DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,4,2))
(or =DATE(RIGHT(A1,4),LEFT(A1,1),MID(A1,3,1)) depending on what was in the original cell.)
where are you located? It worked for me in the US.
You can also do this so that the formulas are all the same:
B2=SEARCH("/",A2, 1)
this is shorthand for select cell B2 and type (or copy and paste from here) the formula:
=SEARCH("/",A2, 1)
C2=SEARCH("/",A2, 1+B2)
D2=RIGHT(A2,4)&"-"&MID(A2, B2+1, C2−B2−1)&"-"&LEFT(A2,B2−1)
select B2 thru D2, copy
select column B thru D, paste
Very ingenious. Thanks!
Thanks, this works — however, I don't understand it at all, can you explain what is happening in more depth?
It should be explained that to actually use the output function data (yyyy/mm/dd) from column D, one has to select it, copy and then paste "formula results" with cmd+shift+v — typical copy/paste just pastes the function.
UPDATE: I found that your formula didnt produce the correct result
It changed mm/dd/yyyy to yyyy/dd/mm — rather than the desired yyyy/mm/dd
By hacking at it, I found that changing the D column formulas around fixed this.
Your original function:
D2=RIGHT(A2,4)&"-"&MID(A2, B2+1, C2−B2−1)&"-"&LEFT(A2,B2−1)
Changed:
D2 =RIGHT(A2,4)&"-"&LEFT(A2,B2−1)&"-"&+MID(A2, B2+1, C2−B2−1)
my mistake... good catch.
Do you have any questions left on this topic?
Convert mm/dd/yyyy text format to date format
