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.
mtarler Sorry if i made this more confusing than it is.
So basically, that formula I got from another website. But ultimately, I am just looking to count the number of cases that fall between a certain date/time range.
Column E is my Date/time Opened
Column F is my SLA Cut Off (basically our 3:00 PM cut off time)
Column G just shows the days in between minus weekends and holidays.
So just trying to figure out, how many cases would be between select date/time ranges?
Example:
Column E: 6/29/2020 2:59 PM through Column F: 7/8/3030 3:00 PM = #XXX of cases, also need it to take into account weekends & holidays if possible? Is there a way to do that?
I can attach the file too if that helps?
Attaching file.
Here is what my spreadsheet looks like:
This is where I'm pulling the data from.
So Is there a way to count how many cases fell between specified date/times?
So if I start with 6/26/2020 1:55 PM and then let's say use a 7/2/2020 2:59 PM cut off date/time. Is there a way to count how many cases fell between that range?
The only way that I am taking weekends and holidays into account is in column G which excludes those items - but I'm worried about the first part first. I may not need to worry about it, if I can get the formula to work between date/time ranges.
I tried using this website for reference - but cannot get it to work for me. Click https://www.get-digital-help.com/count-entries-between-date-and-time-criteria-in-excel/
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.