Google Sheets – How to Format Date in DD/MM/YYYY Format

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.

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