dateformattinggoogle sheets
Whenever entering the date e.g. day-month-year, it automatically changes to month-day-year. I went into format>number>more formats>more date and time formats, and I changed it to day-month-year. And again, when entering day-month-year e.g. 18-4-2014, it changes it to 4-18-2014, but when I click on the date, it shows as 18/4/2014 next to fx above the page.
So it looks like Google Spreadsheet is messing up. How to fix this?
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
Formatting is not the answer, because it doesn't affect the underlying values. You need to make the values updatable instead.
One way is to make a reference-data place somewhere in one of your sheets (or maybe add a new sheet just for that), and put the year value in there.
Then in the first cell where you want the date values to appear, use this formula:
=date(B1,01,01) except replace B1 with the value of the cell where you put the year value.
In the cell immediately above or beside it, say =D1+1 except replace D1 with the reference to the cell where you put the first forumula.
Then to make a new file for next year, just save-as and change the one reference value.
Best Answer
The solution is to set the correct Local under the Spreadsheet's settings ("This affects formatting defaults, such as currency."). So if you want US.locale style (MM-DD-YYYY), then choose US.Locale. If you are in a different locale and want formatting