Changing a date in google sheets from MM/DD to DD/MM without changing the values

Question

I have a survey data set where respondents DOB was entered as "DD/MM/YYYY". Sheets has formatted some of the cells as "MM/DD/YYYY", and where that wouldn't make sense because the day is greater than 12 it has formatted it as text.

I want to change the "MM/DD/YYYY" formatting to "DD/MM/YYYY" formatting, so sheets treats them accurately in formulas, but I don't want it to change the actual values inside the cells as the way they are displayed are accurate.

That is I have some date in a cell "05/10/2001". Sheets is treating that as the 10th of May 2001. The actual value is the 5th of October 2001. If I was to reformat it normally it would change the date in the cell to "10/05/2001". I want sheets to treat it like DD/MM/YYYY without actually changing the date in the cell.

Answer 1

1. Create a new spreadsheet
2. Go to spreadsheet settings and set Region to some country that use the date formatting that corresponds to the survey data, like Mexico. For other countries check https://en.wikipedia.org/wiki/Date_format_by_country
3. Import the survey data

Related

• Formatting Google Sheet Dates Inconsistent
• Change Google Sheets default date format with other locale
• What date formats does Google Sheets recognize?