Dynamic values within a Date Function

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...

Best Answers

  • David Lewis
    David Lewis ✭✭✭
    edited 01/05/21 Answer ✓

    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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!