convert america date format to european

I tried importing a CSV and the dates stubbornly refused to convert to UK from US despite all my regional system and app settings being correct. In addition, because some of the dates don't make sense in UK format (e.g. 07/17/2024) it won't let you set the cell's format to "Date" (there's no 7th day of the 17th month). This is really annoying because it will set some of them to "Date", because they look right but they are still the wrong way round and could look like other dates (e.g. 7/5/24 is 7th May in UK but 5th July in USA), so actually dangerous.

My solution was messy but it saved me hours manually correcting dates on a mileage claim.

1. Keep the bad dates you've got, but set them all to Text format.
2. Make four new columns after the one that contains the bad dates, so there are four new cells next to the ones with the bad dates.
3. Use the MID function in the SECOND new cell along to grab the first two characters for the bad date (this is the month).
4. Do the same in the FIRST new cell with characters 4-5 of the bad date (this is the day).
5. Same again with the last four characters of the bad date in the THIRD new cell (this is the year).
6. Use the DATE function in the FOURTH new cell to join together the values from the first three new cells in order. This cell should be in date format.

In short: split the US date up into month, day and year over three cells, removing the "/"s, then join them back together again in a different order, telling the app it's a date.

My assumption of your problem is:

1. Your computer region is set to one where the dates are DD/MM/YY not the "American" MM/DD/YY
2. You import your CSV into Numbers and the dates come in as MM/DD/YY (same as in theCSV), all formatted as text
3. You want them to be formatted as Date&Time

After importing,

1. Click on the menu item File->Advanced->Language & Region
2. Change the Language from System-English to just English
3. Change the region to United States
4. Format the column of dates into Date & Time format
5. Change back to System-English

Hi snail007,

Numbers is very good at recognising anything that could be a date. In Format Panel > Cell > Data Format > Date & Time there are many choices, depending upon your System Settings > Language & Region.

For example: an "American" date as Month Day, Year

shows these options for the display:

Choosing Day Month Year (shown in the Format Panel as 5 Jan 2023) shows this:

A screen shot of a small part of your csv file will help us to see what you see. Hide any personal data before taking the screen shot.

Regards,

Ian.