You can make a difference in the Apple Support Community!
When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.
When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.
Hello everyone,
I have a problem with Numbers. I should convert from a csv file the date format of the original file (e.g. 2020-03-02T17: 00: 00) into a format compatible with the software. How can I do? The file is quite large and it is difficult to do it by hand because it covers a period of a few months (there are 38,000 rows in the table) with numerous geographical variables. I attach a sample image of the table.
Assuming your computer is set so dates are written as as shown in the table (YYYY-MM-DD),
In a new column, use the formula =SUBSTITUTE(A, "T" , " ")+0
This will create a column of dates with the correct times. The "+0" at the end makes Numbers recognize it as a date vs text. Scan down the column to ensure no error triangles, which might happen if one of the "dates" in column A did not fit the pattern.
You may have to adjust the cell format to show the date and time the way you want to see it.
If you want those results to be in column A,
Assuming your computer is set so dates are written as as shown in the table (YYYY-MM-DD),
In a new column, use the formula =SUBSTITUTE(A, "T" , " ")+0
This will create a column of dates with the correct times. The "+0" at the end makes Numbers recognize it as a date vs text. Scan down the column to ensure no error triangles, which might happen if one of the "dates" in column A did not fit the pattern.
You may have to adjust the cell format to show the date and time the way you want to see it.
If you want those results to be in column A,
Hi seneghe,
Assuming that each row in data (column A) has the same number of characters, you can insert extra columns and use the LEFT and MID functions to pull the data apart.
Formulas (and fill down):
B2 =LEFT(A2,4)
C2 =MID(A2,6,2)
D2 =MID(A2,9,2)
E2 =MID(A2,12,2)
F2 =MID(A2,15,2)
G2 =MID(A2,18,2)
H2 =DATE(B2,C2,D2)
I2 =TIME(E2,F2,G2)
You might want to Save as Template for future use.
Select and copy columns H and I then Menu > Edit > Paste Formula Results.
Then you can delete columns B to G.
Regards,
Ian.
Let's start from the point that I use the Italian version of Numbers, so the formulas are different.
B2 = LEFT (A2,4) works fine and extracts the first four digits of the date. While the formulas C2 = MID (A2,6,2) ... don't work. I guess the MID command is MEDIANA (?). Or maybe something else? Should the cells in Column A be in date, number or text format?
You can switch from Italian to English at File > Advanced > Language and Region. Enter the formula in English. Then switch back to Italian and you will see the equivalent formula in Italian.
SG
Hi seneghe,
In Italian, LEFT is SINISTRA
MID is STRINGA.ESTRAI
In Badunit's formula, SUBSTITUTE is SOSTITUISCI
Saluti,
Ian.
Neat!
Did SUBSTITUTE(A, "T" , " ")+0 (or the Italian equivalent of it) not work?
Date conversion in text-to-date format compatible with Numbers
