google sheets
I have to do a query that increments automatically the date year by year. For example I use this formula:
=INDEX(query('Scheda Intervento'!C3:K14;"select sum (K) where (F >= date '2013-01-01' and F <= date '2013-01-31' and C = '" & A3 &"')");2;1)
But every year I have to change the date in 2014 , 2015 etc.
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 following formula will help you filter the dates.
=FILTER(sourceArray, arrayCondition_1,..., arrayCondition_30)
=SUM(FILTER(B2:B, RIGHT(A2:A, 4)="2012", LEFT(A2:A,LEN(A2:A)-5)="february"))
The first arrayCondition_1 retrieves the year, having 4 digits, taken from the RIGHT. The second arrayCondition_2 retrieves the month (text), based on the total length of the string minus 5 (digits year plus white space). The sourceArray in filtered accordingly and the result is summed up.
I've created an example file for you: FILTER DATE AS TEXT
Best Answer
Try this
I used the function YEAR(NOW())