google sheetsgoogle-sheets-dates
Going to a new cell, if I type 07/22, if the column data format is set to a date, it interprets this to July 22, 2020 (where 2020 is the current year). I want it to use the 22 as the year, I don't mind if behind the scenes it translates to July 1, 2022 or July 31, 2022 (although I prefer the 1st).
I have the data column set to Month (08)/Year (30) i.e. expected output is exactly as typed 07/22. But even with this on, Sheets interprets the entry as Month, Day.
Ultimately, you need to do what you already predicted: adding a leading zero. The TEXT function will make that happen like this:
=TEXT(A2, "00")
I've created a formula that takes on the complete column, filter for empty cells, brings together the MONTH and DAY and SORTs the lot.
=SORT(ARRAYFORMULA(TEXT(MONTH(FILTER(A2:A;A2:A<>""));"00") & " - "
& TEXT(DAY(FILTER(A2:A;A2:A<>""));"00"));1;1)
Here's a break-down description of the formula:
FILTER function will filter for all rows that have something.MONTH and DAY functions will extract the respective values
from the date.TEXT function will convert the value from point 2 into a pre-formatted STRING.ARRAYFORMULA function will apply all the above to the complete column (skipping the header).SORT function will sort the result, given by the
ARRAYFORMULA.The SORT function allows for sorting. If you do that via a column sort, the entry of the ARRAYFORMULA is taken into account as well and gets re-positioned, causing mayhem.
See example file I've created: sorting dates as text
If formatted as text at data entry, those are the results, if formatted as a date (say dddd, dd mmm, yyyy) the appearance will be the same but the date will increment by a day at a time.
Best Answer
The behaviour cannot be changed.
That is what you need to write as well.
This is just the formatting.
You still have to write a date as
day/month/yearormonth/day/yearpending on your locale.("When typing a date into cell as 12/24". Just think that this could be interpreted as hours, as a fraction etc...)