Google sheets date

Question

I have a a google sheets with dates in different formats in a column which I am trying to sort chronologically (some formats are d/m/y and some are m/d/y) . I am trying to change them all to the one format.

I want my dates in the structure of day/month/year.

As an example in one cell I have 12/01/2021 (mean 12th of January 2021), google sheets sees this as 1st of December 2021, when I change the date format to d/m/y it changes the data in the cell to 01/12/2021.

Is there a way to change the date format but keep the structure?

In summary, I have 12/01/2021 in a cell. When I change the date format to d/m/y it changes the contents of the cell to 01/12/2021. Is there anyone way to change the date format without google sheets swapping the month and day numbers?

Answer 1

All dates in Google Sheets are actually the number of days elapsed since December 31, 1899. For instance, Google Sheets records the date January 12, 2021 as 44208 (or 44,208 days since December 31, 1899).

Changing the format of that number does not change the underlying number. That is, whether you show the format as 01/12/2021, 12/01/2021, 2021-Jan-12, or anything else, memory will still see the number value as 44208.

I'm not sure how you are visibly able to look at the data and know whether 12/01/2021 was supposed to be January 12 or December 1, since you said you have mixed dates. But if there is some way, then it's possible to set up a formula that can convert the chosen dates by flipping the month and day permanently, after which you could copy the results back into the original column. But changing format alone will never change the actual value of the underlying date-number of the original/raw data; you must process it (with script or formula) to get a different underlying date.