Forum Discussion
Counting entries between date/time ranges
NeilKloster in the attached is a SUMPRODUCTS() formula that I used because the COUNTIFS() formula wouldn't work because the "dates" in your table are actually TEXT format (you probably imported or copied them in as text). I also went to the DATA tab and used "Text to Columns" to force that column to be actual Date information instead of text and included the correct COUNTIFS() formula in the cell below the other "count" cell to show how it works (that it does work if you correct the text). If you will be importing data a lot and don't want to deal with converting text to number every time use the first. If you want to do more with the data and dates, you might want to convert the text to real dates to make it easier for yourself.
Hi again mtarler
Was hoping that I could ask for your assistance again? So the report that you sent me, helped me get to where I'm at - but I am stuck because I see that the formula you gave me, for some reason it's not properly counting up the entries. Was wondering if you could take a look again?
For example,
For my BSN count, it is currently showing 505 for 4 business day (4 BD), but when I look at the tab for BSN Evaluations (where the data is at) and count from the date ranges from: 6/30/2020 10:44 AM - 7/6/2020 2:59 PM it should be: 331 (when I count it manually). So do you know why it would be counting more than that?
I attached my file here to the response to see if this helps make any sense?
Thank you for your help!!
NeilKloster i found 2 problems.
1. your > and < references were reversed. You had < smaller date and > larger date. I'm guessing you might have been 'playing' with the formula and accidentally left it that way
2. the "real" problem is that your "dates" in Q5 and Q12 are really text so you have the same problem you had before. You can simply add the -- in front of those 2 refs in the formulas or fix those 2 cells.
I think you can make those corrections (and see/learn) but if you need more guidance just ask.