Google-sheets – Date format in spreadsheet (google doc)

dategoogle sheets

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?

Best Answer

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.

Formula

=JOIN(
   CHAR(10),
   ARRAYFORMULA(
     QUERY(
       Transactions!A2:D, 
       "SELECT B WHERE A = date '" & text(Summary!A2,"yyyy-MM-dd") & "'"
     ) 
     & ": " &   <=== concatenate
     QUERY(
       Transactions!A2:D, 
       "SELECT D WHERE A = date '" & text(Summary!A2,"yyyy-MM-dd") & "'"
     )
   )
 )

 copy/paste         
 =JOIN(CHAR(10),ARRAYFORMULA(QUERY(Transactions!A2:D, "SELECT B WHERE A = date '" & text(Summary!A2,"yyyy-MM-dd") & "'") & ": " & QUERY(Transactions!A2:D, "SELECT D WHERE A = date '" & text(Summary!A2,"yyyy-MM-dd") & "'")))

Explained

The two QUERY functions, both deliver each part of the data you want to present: the description and the value. These two are then "glued" together, via the ampersands and per row, via the ARRAYFORMULA. Each row is then joined, via the JOIN function, using a line feed (CHAR(10)) as delimiter.

Screenshot

enter image description here

Note

The huge benefit over a script is the immediate re-calculation of the data, when changes occur. Just copy the formula downwards to get all the results. You can add your own logic to remove empty fields.

Example

I've prepare an example file for you: Notes

Google-sheets – Google Spreadsheets function to filter by date and count text strings in certain fields

So, basically, you are counting the rows where (among other conditions), the G column cell is exactly the text on Tables!D2. And, instead, you want to count where the G column cell contains the text on Tables!D2. Am I right?

You can do that with the following change to your formula. Replace:

TRIM('2014 Form Data'!$G:$G)=Tables!$D$2

With:

ISNUMBER(FIND(Tables!$D$2;'2014 Form Data'!$G:$G))