Date format in spreadsheet

Question

I want to create spreadsheets to record the money I spend each year. Each file is named as Cost-2012, Cost-2013,...etc. Inside each file, there are in fact 12 sheets, which are named as 01, 02, ..., 12, recod the usage of money each month.

For each sheet, I want the top row look like 09-01, 09-02, ...., 09-30, (09-31), the first number represents the month.

Google spreedsheet has build-in date formate, eg. 9/01 or 09-01. I used it and they looked good. But when I clicked the cells, they looked as the following picture shows:

It works fine if I don't use this file as a template, which I will copy and rename the newly copied file as 2014, 2015...etc.

The 3 (related) questions I have are:

First: When 2014 comes and I want to review what happened in 2013, do the dates in these cells (in the file Cost-2013) will remain 2013 (i.e they don't change to 2014 automatically)? I guess yes. But if so, it causes the 2nd question.

Second: I want to create a template. Say it is named as Cost-Template. When I create this template now, so the value (actual value, not the values displayed, see the following picture) in each cells on the top row ends in fact with 2013. Now when 2014 comes, so I copy this template, rename it as Cost-2014 and open it. But the (actual) values of the date cells are still xx-xx-2013. How can I change all of them (in all the 12 sheets) quickly?

Third: Or I should not use this build-in date format and use, say, Number format? Or there are other build-in date formates which avoid the problem in the 2nd question?

Answer 1

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.