Google sheets date

0
votes

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?

1
google-sheets
It do not changes content only changes formats means view. Your formatting is working as expected. What do you want to show in cells? – Harun24HR
I want to show 12/01/2021, at the moment the cell shows 12/01/2021 (but the format is m/d/y, I want d/m/y), when I change the date format it changes the cell to 01/12/2021. I want the day to be 12 and month 01. – Henrick
Google sheet by default use m/d/y format. if you want d/m/y then format cell and entry dates like 1/12/2021. – Harun24HR
You can change the format to d/my/y but as in my example above the values for day and month change position. I want the 12 to remain as the first number but to be changed to the day. – Henrick

1 Answers

1
votes

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.