Having problems with Date Ranges

Options

I want to count install for each month.

=COUNTIFS({Install Date5}, "Install Date", {Install Date9}, AND(@cell >= DATE(5,01,2024). @cell <= DATE(5,31,2024)))

Tags:

Answers

  • Kerry St. Thomas
    Kerry St. Thomas ✭✭✭✭✭
    Options

    I preface this by recommending you NOT do a summary at the top of the sheet (because that'll prevent you from using column formulas, and will be problematic in terms of scaling once this sheet rolls over to June and July and so on). Instead, add a column with a column formula that converts the Install Date to a Year/Month… for example, 2024-05 :

    =YEAR([Install Date]@row)+IF(MONTH([Install Date]@row)<10,"-0","-")+MONTH([Install Date]@row)
    (The "IF" part in the middle of the formula adds a leading 0 to one-digit rows.)

    You can then create a Report, grouping by that new column and summarizing with COUNT.


    I find that with dates, COLLECT is a little more forgiving than COUNTIF/S. So with that said, the formula I would use for the UNPARSEABLE cell on line 2 is:

    =COUNT(COLLECT([Quote Number]:[Quote Number], [Install Date]:[Install Date], >=Status@row, [Install Date]:[Install Date], <=[Internal Owner]@row))

    If this answer resolves your question, please help the Community by marking it as an accepted answer. I'd also be grateful for your response - "Insightful"or "Awesome" reactions are much appreciated. Thanks!

  • Hitnar
    Options

    @Kerry, St. Thomas, I really appreciate your prompt response. The first formula does not fit the case I was looking for. The second one more what I would need. Do I need to convert the date to Year-Month-Day for it to work?

    BTW - Your profile says St. Thomas, is that the Virgin Island or Jamaica?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!