# How to use a single reference cell to change a year reference in a year-month-date across many formulas?

How do I change the date reference in the formula below: `>=2018-1-01` and `<=2018-1-31`, so that it will refer to a single cell date in cell `J4` correctly? The formulas below work correctly, no problem!

(E8) `=SUMIFS(D\$44:\$D\$84,\$B\$44:\$B\$84,">=2018-1-01",\$B\$44:\$B\$84,"<=2018-1-31")`

(F8) `=SUMIFS(D\$44:\$D\$84,\$B\$44:\$B\$84,">=2018-2-01",\$B\$44:\$B\$84,"<=2018-2-28")`

(G8) `=SUMIFS(D\$44:\$D\$84,\$B\$44:\$B\$84,">=2018-3-01",\$B\$44:\$B\$84,"<=2018-3-31")`

etc.

The difficulty is that each year I have to manually go in and change every reference to the new year in the formulas, and worse, have to ask people in our volunteer group using the expense report who are not familiar with Excel to go into formulas and edit them. Not very practical on the last part! In cell `J4` if I can just change the date to `2019` next year and have the formulas change, well, everybody will be able to manage that!

Adding a simple cell reference does not work, i.e.:

``````=SUMIFS(D\$44:\$D\$84,\$B\$44:\$B\$84,">=(J4)-1-01",\$B\$44:\$B\$84,"<=(J4)-1-31")
``````

Other variations similar to just adding `J4` reference in the formula above do not work.

Other variations such as `=(DATE(YEAR(J4),MONTH(1),DAY(1)))` work as a stand-alone reference, but not in the formula. (It often returns `0`.)

The first part of the formula, `=SUMIFS(D\$44:\$D\$84,\$B\$44:\$B\$84,)` is necessary to look up expenses from another table by the month and place them in the monthly columns as per the screen shot. So changing that part of the formula (which works fine) is not an option.

I have tried all kind of date references in the formula and if I don't get an error message, I simply get `0`.

I'm using Excel 2011 (mac)

I think what you want is the following:

=SUMIFS(D\$44:\$D\$84,\$B\$44:\$B\$84,">="&J4&"-1-01",\$B\$44:\$B\$84,"<="&J4&"-1-31")