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.

  • Welcome How are you entering the survey data to the spreadsheet? Are you importing it, copy/pasting, using a Google Form or another mean? Have you already made any search/research effort to fix this values? Ref. How to Ask. Sep 10, 2019 at 2:16
  • I did research it, but I couldn't find anyway to do it without altering the values in the cell. I'm importing the data from a webform app (similar to google forms), but the data has already been collected.
    – Gumboots
    Sep 10, 2019 at 3:13

1 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


  • Worked a charm, crazy how much time I spent on such a simple fix.
    – Gumboots
    Sep 10, 2019 at 5:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.