SOLVED

Counting entries between date/time ranges

Brass Contributor

Hello, 

 

I'm trying to figure out how to count the number of entries in an excel column that fall within a certain date/time range?

 

For example, I have a spreadsheet that has three columns (See below): 

 

Date/time case opened

SLA cut off (date/time of our cut off date/time)

Workday Hours - excluding weekends/holidays - represented in days, hours, minutes

Capture5.JPG

 

What I need to do is figure out a way to count the number of cases between unique date/time ranges?

 

Example: 

Capture65.JPG

I found this website that seems to give this formula for it's scenario:

 

=COUNTIFS(B3:B17, "<="&F3, B3:B17, ">="&F2, C3:C17, F4)

 

but I cannot get it to work for my specific scenario - any help or advice?

 

9 Replies

@NeilKloster  without the sheet it is hard to know for sure.  for example I assume the dates in the table are in B3:B17 and the start date is in F2 and the end date is in F3 but what is that last part at looking at column C and checking it again F4?  What is F4?  Based on the picture it would appear to be "Item" which is blank.  Since none of the SLA Cutoff cells are blank the count should be 0.  In F4 try putting 7/8/2020 3:00 PM or just delete that part of the equation so you only have: 

=COUNTIFS(B3:B17, "<="&F3, B3:B17, ">="&F2)

 

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

 

 

yes including the sheet would help. If col E is opened and F is cut off and you want count of cases that fall between that then where are the dates for those cases? a different table/sheet? How would a COUNT take into account weekends & holidays? either the date is between those dates or not.

@mtarler 

 

Attaching file.  

 

Here is what my spreadsheet looks like: 

Capture6f5.JPG

 

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

best response confirmed by NeilKloster (Brass Contributor)
Solution

@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 

 

Thank you!!  It worked!! 

you're very welcome

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.

1 best response

Accepted Solutions
best response confirmed by NeilKloster (Brass Contributor)
Solution

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

View solution in original post