# How to change date value in formula

I am using this formula in Google Sheets:

``````=sumifs('Fieldwork Log'!M:M,'Fieldwork Log'!G:G, ">="&date(2022,8,1),'Fieldwork Log'!G:G,"<="&date(2022,8,31))
``````

It returns the sum of time durations for each month. Here is a link to the sheet: Fieldwork Tracker
I would like to drag the formula down a column but have the dates inside the `">="&date(2022,8,1)` and `"<="&date(2022,8,31))` move forward chronologically. Can anyone help? Cant find any answers anywhere on the web and I have others using the worksheet on their own with zero experience with spreadsheets.

• @darkdiamond thank you for your suggestion, new user here.
– Anthony
Jul 28 at 19:25
• you're welcome! This is what the review queue for first questions is for. Accept from that, your question is well asked.
– DarkDiamond
Jul 28 at 19:30
• I think I just solved my own problem. I can reference the date from a list that shouldnt take long to make.
– Anthony
Jul 28 at 19:32
• Then please post your solution as an answer to your own question so that everyone else that comes here can solve the problem as well
– DarkDiamond
Jul 28 at 19:35
• Instead of the formula having the numbered date e.g.,(2022,8,1), create 6 columns for each (y,m,d)1 and (y,m,d)2. After manually creating 2 chronological rows drag down to complete sequence for that year, repeat for next year. When creating the formula, instead of using digits, reference the cells that you created ex. (A1, A2, A3) and (A4, A5, A6). Drag until you the last values you are referencing. I hope that is clear. Link to spreadsheet with my solution is here: docs.google.com/spreadsheets/d/…
– Anthony
Jul 28 at 19:44

Choose Insert > Sheet and put this formula in cell `C6` of the new sheet:

``````=arrayformula( query(
{
eomonth('Fieldwork Log'!G6:G, -1) + 1,
value( { 'Fieldwork Log'!J6:J, 'Fieldwork Log'!M6:M, 'Fieldwork Log'!P6:P } )
},
"select Col1, sum(Col2), sum(Col3), sum(Col4)
where Col1 is not null
group by Col1
label sum(Col2) '', sum(Col3) '', sum(Col4) '' ", 0
) )
``````

This array formula will list all the months where data exists and calculate the totals without the need to drag down. To get "Total Experience Hours", put this formula in cell `G6`:

`=arrayformula( iferror( 1 / sumif(if(column(D6:F), row(D6:F)), row(D6:F), D6:F) ^ -1 ) )`