I have a csv file with data I want to load into Numbers. The csv file contains a column with dates (no time). The dates are formatted as "DD.MM.YYYY", e.g. "16.12.2019" for 12. dec 2019.
How do I load this into Numbers and have the column properly recognized as dates?
Please do not include any answers that requires me to change the date format in macOS.
You could use the function SUBSTITUTE for changing the " . " to " / ", like this:
Substitute modifies 16.12.2019 to 16/12/2019. The format is Automatic, hence still "TEXT", but Numbers will recognise the content as a date as shown in the next cell D2.
Formula in D2: C2 – 0, to keep the same date, then formatted as shown.
Paul.
Hi Nicolai,
No need to change the date format in macOS.
Try this. It involves some work, but you can hide the intermediate columns and Save as Template for future use.
Fill these formulas down.
Formula in B2 =LEN(A2)
Formula in C2 =FIND(".",A2,1)
Formula in D2 =FIND(".",A2,C2+1)
Formula in E2 =RIGHT(A2,B2−D2)
Formula in F2 =MID(A2,C2+1,D2−C2−1)
Formula in G2 =LEFT(A2,C2−1)
Formula in H2 =DATE(E2,F2,G2)
Column I shows how you can format the date to your preferred display.
Yes, some work, but when you hide the "work" columns and Save as Template:
Regards,
Ian.
Nice solution, Paul!
Ian.
How to convert text to date in Numbers
