Having problems with Date Ranges

Hitnar

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

Kerry St. Thomas

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

Hitnar

@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?