Dynamic values within a Date Function

David Lewis

Is it possible to load ranges dynamically within a date function? I would imagine having a separate sheet with the beginning and ending dates for every month of the current year, and then referencing those within date functions. Example: =COUNTIFS({Sheet Reference for apples eaten}, >=DATE(*), {Sheet Reference for apples eaten}, <=DATE(*))

Where * is the reference to the beginning and ending date for, say, January.

The output of this would be the number of apples eaten in January this year.

The reason for doing this dynamically, if not obvious, is that if I am going to have a sheet that displays the number of apples eaten every month of the year, lets say for all the members of my team (12 rows), that is 144 cells. And if I have to update that next year to 2022 dates, it would be nice to just have to update 24 cells in the "helper sheet", rather than 144.

Hope this makes some sort of sense...

David Lewis

Paul thank you for your comment. I probably didn't explain what I am trying to do well. I believe that I need to use the DATE function in order to evaluate if any records are within a given month. Here is an example of the hard coded date format I am using to evaluate:

=COUNTIFS({reference to column in sheet with data I am trying to count}, >=DATE(2021, 1, 1), {reference to column in sheet with data I am trying to count}, <=DATE(2021, 1, 31))

What would be great is if I could replace the values within the parenthesis of the DATE function with a reference instead of having them hard coded.

Paul Newcome

Yes. I understand that you are trying to count within a given month. You can use MONTH and YEAR functions to not have to worry about specific dates since some months can have anywhere from 28 to 31 days.

You could then say

AND(MONTH(@cell) = 6, YEAR(@cell) = 2020)

to get a count for all of June 2020 and not have to worry about how many days are in that particular month or building out a table with the start and end dates for every month which would then require 12 cells being updated

You would replace the 6 and the 2020 with cell references containing the month and year accordingly so that you can update those two cells to update all formulas (instead of the 12 if using the date table).

I incorporated the IFERROR statements to avoid any issues with blanks and/or non-date values within the range.

Paul Newcome

You could add the month and year into two separate cells and reference them in your formula.

=COUNTIFS({Date Column}, AND(IFERROR(MONTH(@cell), 0) = [Column Name]1, IFERROR(YEAR(@cell), 0) = [Column Name]2))

Then you would only have to update two cells. One for the month and one for the year that you are wanting to count for.

David Lewis

Paul thank you for your comment. I probably didn't explain what I am trying to do well. I believe that I need to use the DATE function in order to evaluate if any records are within a given month. Here is an example of the hard coded date format I am using to evaluate:

=COUNTIFS({reference to column in sheet with data I am trying to count}, >=DATE(2021, 1, 1), {reference to column in sheet with data I am trying to count}, <=DATE(2021, 1, 31))

What would be great is if I could replace the values within the parenthesis of the DATE function with a reference instead of having them hard coded.

Paul Newcome

Yes. I understand that you are trying to count within a given month. You can use MONTH and YEAR functions to not have to worry about specific dates since some months can have anywhere from 28 to 31 days.

You could then say

AND(MONTH(@cell) = 6, YEAR(@cell) = 2020)

to get a count for all of June 2020 and not have to worry about how many days are in that particular month or building out a table with the start and end dates for every month which would then require 12 cells being updated

You would replace the 6 and the 2020 with cell references containing the month and year accordingly so that you can update those two cells to update all formulas (instead of the 12 if using the date table).

I incorporated the IFERROR statements to avoid any issues with blanks and/or non-date values within the range.

David Lewis

This worked very well, thank you Paul

Paul Newcome

Happy to help. 👍️