Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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:

enter image description here

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?

share|improve this question

migrated from superuser.com Mar 8 '13 at 2:49

This question came from our site for computer enthusiasts and power users.

add comment

1 Answer

up vote 3 down vote accepted

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.

share|improve this answer
    
And I totally agree, this is soooo not a web-apps question. But I figured it was better to just answer it and let the mods worry about categories. –  MaryC.fromNZ Mar 8 '13 at 11:22
    
Thank you. By the way, it's not me (the author) to do the migration from superuser.com to here. –  user565739 Mar 8 '13 at 11:53
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.