How do I count the occurrences within a time range for Excel

I am trying to get number of occurrences between a time frame.  I have used the countif and sumproduct formulas.  But excel is not discerning between AM and PM.  For example I am trying to get occurrences between 5:00:00 AM and 6:00:00 PM, but it just gives me total occurrences between 5:00:00 and 6:00:00 regardless of AM or PM.
bjew2112Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JohnBusiness Consultant (Owner)Commented:
Take the difference between 5:00 and 18:00 .  That should work fine. Excel recognizes a 24 hour clock.
0
Brad RubinCommented:
Try this: =COUNTIF(B2:B25,">=05:00")-COUNTIF(B2:B25,">18:00")

Where Column B has a range of times...should work...I am using 24 hour time in Excel.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
By occurrences, do you mean unique occurrences?
If so, assuming your time is in col. B in the range B2:B25, then try this Array Formula (which requires confirmation with Ctrl+Shift+Enter instead of Enter alone) in D2

In D2
=SUM(--(FREQUENCY(IF(B2:B25>=TIMEVALUE("05:00:00"),IF(B2:B25<=TIMEVALUE("18:00:00"),B2:B25)),B2:B25)>0))

Open in new window

Is this what you are trying to achieve?

For details refer to the attached workbook.

**************************
Remember that an Array Formula is confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.  You will know the array is active when you see curly braces { } appear around your formula.  If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.  Press F2 on that cell and try again.
Time-Occurrence.xlsx
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Saurabh Singh TeotiaCommented:
You can simply do this...which will do what you are looking for...

=SUMPRODUCT((B2:B25>=TIMEVALUE("05:00:00 AM"))*(B2:B25<=TIMEVALUE("06:00:00 PM")))

Open in new window


Enclosed is the example file for your reference as i used sktneer example file only..

Sktneer..Correct answer is 16 so you might want to look at your formula as it's giving an incorrect answer..

Saurabh...
Time-Occurrence.xlsx
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@ Saurab

I think you were not able to understand the formula suggested by me. Were you? :)

Should I explain it? :)

It is counting the unique occurrences of times between 5:00:00 AM and 6:00:00 PM.

And yours is counting all the occurrences not the unique ones. Clear?

Read my post where I questioned the OP if he wanted to count the unique occurrences before suggesting him a formula.

So please read the whole post before commenting on someone's solution. :)

Thanks.
0
Saurabh Singh TeotiaCommented:
Sktneer,

My bad..I thought you were asking that question but didn't realize you designed the formula to do that..

Also i sort of validated the data before i posted but didn't see from duplicate prospective.. i understand where is the difference now.. :-)

Thanks for correcting me .. :-) And apologies for this confusion.. :-)

Saurabh...
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
No problem Saurabh!
Glad that you understood my point.

Though we both are not clear about the actual requirement of the OP.
Let's wait and see what does he actually need.


Thanks.
0
bjew2112Author Commented:
I am sorry.  I meant between 5:00 AM and 6:00 AM not PM.  It will only give me 11 occurrences.  There are many more than that.  There is a month's worth of scan in times.  If you count the first 2 days, you come up with 11.  I don't know if that is a coincindence.  I can't attach the file.  Here is a litle bit of it.
                                                    Scan In Time
A88499               101948      5:18:07 AM              5:18:32 AM
695754               101948      5:22:48 AM              5:22:58 AM
A89014               101948      5:34:00 AM              5:34:08 AM
695753               101948      5:46:59 AM              5:47:07 AM

                                                                                                                                         Start      5:00:00 AM  (in K7 cell)
                                                                                                                                         End      6:00:00 AM  (in K8 cell)

=SUMPRODUCT((E7:E2459<=K8)*(E7:E2459>=K7))
=COUNTIF(E7:E2459,">="&K7)-COUNTIF(E7:E2459,">="&K8)

Thank you all for your responses.
0
bjew2112Author Commented:
It is just counting the first 2 days and ignoring the whole rest of the month for some reason.  I have had to import the data.  It had the dates in with the scan in times, but I went through and eliminated the dates to help with the formulas.  Not sure what is going on.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Make a dummy workbook with enough data and upload it here with your desired output mocked up manually.
Remember to remove an sensitive information.
0
bjew2112Author Commented:
Here is the file without sensitive information.
workbook.xlsx
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
It is because the time in column E from row177 downwards are not the time but text, remember excel treats date and time as a number. Those time look like time but actually are text.

Try this formula to get the count between the time boundary set by you.

=SUMPRODUCT((TRIM(E7:E2459)*1<=K8)*(TRIM(E7:E2459)*1>=K7))

Open in new window


Let me know if you get the correct output.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Saurabh Singh TeotiaCommented:
Like  sktneer posted these values are not actual time values and it has spaces on top of it and now because of these spaces it becomes text value from time..you can alternatively do something like this to do what you are looking for..

=SUMPRODUCT((--TRIM(E7:E2459)>=TIMEVALUE("05:00:00 AM"))*(--TRIM(E7:E2459)<=TIMEVALUE("06:00:00 AM")))

Open in new window


Saurabh...
1
bjew2112Author Commented:
Both formulas worked Saurabh and sktneer.  They gave me exactly what I needed.  Thanks for your help!  I should have just attached it to begin with and saved a lot of trouble.  Lesson learned.  But I have to ask.  How could you tell those rows were text?  I made sure to format them all to time.   I don't understand what is meant by the spaces on top Saurabh.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
In a new column type =ISNUMBER(E2) and drag it down until the last row in col. E, you will get True for the first few times and later you will get False. In a time cell in col. E where your new formula return False, click on the formula bar, and press the home tab you will notice that there are some spaces in the beginning of the time and that makes it a text rather than a number. That's why I used Trim function to remove any leading or trailing spaces in the cell. If you manually remove the leading spaces from the time cell, it will convert to time again. but since there are many time cells with this error and you are exporting the time data from an external source, the smart way to use Trim in the count function.

Also since you are using the time boundaries in the Cells K7 and K8 so no use of hard coding start and end time in the formula, rather use cell references, so that if you change K7 and K8, the formula will return count of times between another time range.
1
bjew2112Author Commented:
It makes sense to me now.  Thank you sktneer.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Glad I was able to clear your doubts.

If that resolves your issue, please take a moment to choose an acceptable answer to your question to mark this thread as Solved.

Thanks.
0
Saurabh Singh TeotiaCommented:
Also the quickest way to find whether its a text or not is just select column--press ctrl+1 and format the data as number...if you see your time values getting changed to decimal points like 0.2343 something that means its a proper time values..However if you see it's not getting changed it means its not a time values..its a text values..

And by spaces i mean if you press f2 and go on the cell you will find the ones which are text start with space..
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.