Count cells between 9 am and 5 pm in a month.

E

excelnewbie34

New Member
Joined
Apr 9, 2014
Messages
7
HI,
I receive data in the format below. I would like to check how many entries were there between 9 am and 5 pm for the range of date specified. Normally it is for a month(01/03/2014-31/03/2014). Is there a formula that I can use to find the total number of entries that fall within this period. I use excel 2007.
[TABLE="width: 185"]
<TBODY>[TR]
[TD]04/04/2014 22:51</SPAN>[/TD]
[/TR]
[TR]
[TD]04/04/2014 22:55</SPAN>[/TD]
[/TR]
[TR]
[TD]04/04/2014 23:21</SPAN>[/TD]
[/TR]
[TR]
[TD]05/03/2014 10:29</SPAN>[/TD]
[/TR]
[TR]
[TD]05/03/2014 10:52</SPAN>[/TD]
[/TR]
[TR]
[TD]05/03/2014 11:26</SPAN>[/TD]
[/TR]
[TR]
[TD]05/03/2014 14:35</SPAN>[/TD]
[/TR]
[TR]
[TD]05/03/2014 14:52</SPAN>[/TD]
[/TR]
[TR]
[TD]05/04/2014 10:03</SPAN>[/TD]
[/TR]
[TR]
[TD]05/04/2014 10:43</SPAN>[/TD]
[/TR]
[TR]
[TD]05/04/2014 16:39</SPAN>[/TD]
[/TR]
[TR]
[TD]05/04/2014 20:55</SPAN>[/TD]
[/TR]
[TR]
[TD]05/04/2014 21:28</SPAN>[/TD]
[/TR]
[TR]
[TD]05/04/2014 21:59</SPAN>[/TD]
[/TR]
[TR]
[TD]06/03/2014 09:41</SPAN>[/TD]
[/TR]
[TR]
[TD]06/03/2014 15:37</SPAN>[/TD]
[/TR]
[TR]
[TD]06/03/2014 17:35</SPAN>[/TD]
[/TR]
[TR]
[TD]06/04/2014 11:25</SPAN>[/TD]
[/TR]
[TR]
[TD]06/04/2014 12:44</SPAN>[/TD]
[/TR]
[TR]
[TD]06/04/2014 14:09



Thank you.

You are doing a great job by running this forum. Thanks you.</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
I can't really think of a different way to do it then by putting the start and end dates and times in separate cells...

Excel 2012
ABCDEF

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFF00"]Dates and Times[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00"]From[/TD]
[TD="bgcolor: #FFFF00"]To[/TD]
[TD="bgcolor: #FFFF00"]In[/TD]
[TD="bgcolor: #FFFF00"]Out[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]3/5/14 10:29[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3/1/2014[/TD]
[TD="align: right"]3/31/2014[/TD]
[TD="align: right"]9:00[/TD]
[TD="align: right"]17:00[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]3/5/14 10:52[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3/5/14 11:26[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00, align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]3/5/14 14:35[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]3/5/14 14:52[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]3/6/14 9:41[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]3/6/14 15:37[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]3/6/14 17:35[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]4/4/14 22:51[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]4/4/14 22:55[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]4/4/14 23:21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]4/5/14 10:03[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]4/5/14 10:43[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]4/5/14 16:39[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]4/5/14 20:55[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]4/5/14 21:28[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]4/5/14 21:59[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]4/6/14 11:25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]4/6/14 12:44[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]4/6/14 14:09[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D4[/TH]
[TD="align: left"]=SUMPRODUCT((INT(A2:A21)>=C2)*(INT(A2:A21)<=D2)*(MOD(A2:A21,1)>=E2)*(MOD(A2:A21,1)<=F2))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
[TABLE="width: 985"]
<colgroup><col width="210" style="width: 158pt; mso-width-source: userset; mso-width-alt: 7680;"> <col width="64" style="width: 48pt;"> <col width="27" style="width: 20pt; mso-width-source: userset; mso-width-alt: 987;"> <col width="64" style="width: 48pt;"> <col width="948" style="width: 711pt; mso-width-source: userset; mso-width-alt: 34669;"> <tbody>[TR]
[TD="width: 210, bgcolor: white"]4/4/2014 22:51[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]09:00[/TD]
[TD="width: 27, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent, align: right"]8[/TD]
[TD="width: 948, bgcolor: transparent"]=SUMPRODUCT(--(--TEXT(A1:A20,"hh:mm")>=B1),--(--TEXT(A1:A20,"hh:mm")<=B2),--(A1:A20>=B3),--(A1:A20<=B4))[/TD]
[/TR]
[TR]
[TD="width: 210, bgcolor: white"]4/4/2014 22:55[/TD]
[TD="bgcolor: transparent, align: right"]17:00[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 210, bgcolor: white"]4/4/2014 23:21[/TD]
[TD="bgcolor: transparent, align: right"]1-May[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 210, bgcolor: white"]5/3/2014 10:29[/TD]
[TD="bgcolor: transparent, align: right"]31-May[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 210, bgcolor: white"]5/3/2014 10:52[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 210, bgcolor: white"]5/3/2014 11:26[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]one more…[/TD]
[/TR]
[TR]
[TD="width: 210, bgcolor: white"]5/3/2014 14:35[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 210, bgcolor: white"]5/3/2014 14:52[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 210, bgcolor: white"]5/4/2014 10:03[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 210, bgcolor: white"]5/4/2014 10:43[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 210, bgcolor: white"]5/4/2014 16:39[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 210, bgcolor: white"]5/4/2014 20:55[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 210, bgcolor: white"]5/4/2014 21:28[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 210, bgcolor: white"]5/4/2014 21:59[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 210, bgcolor: white"]6/3/2014 09:41[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 210, bgcolor: white"]6/3/2014 15:37[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 210, bgcolor: white"]6/3/2014 17:35[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 210, bgcolor: white"]6/4/2014 11:25[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 210, bgcolor: white"]6/4/2014 12:44[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 210, bgcolor: white"]6/4/2014 14:09[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
i don't know, I flipped the dates to DMY because of month(01/03/2014-31/03/2014) so I assumed they were UK dates
 
Upvote 0
Thank you very much for fast reply. I tried both options. First one worked for me very well and yes I am using uk time.
Second one worked when I replicated the cells exactly the way as you shown, but didn't work for the spreadsheet. I must be doing something wrong there. I have to go back and try again with fresh eyes.
Thank you for finding time to help me with this, I have a long way to go before I even attempt to try something like this.
 
Upvote 0
excelnewbie34 said:
Thank you very much for fast reply. I tried both options. First one worked for me very well and yes I am using uk time.
Second one worked when I replicated the cells exactly the way as you shown, but didn't work for the spreadsheet. I must be doing something wrong there. I have to go back and try again with fresh eyes.
Thank you for finding time to help me with this, I have a long way to go before I even attempt to try something like this.
Click to expand...

'=SUMPRODUCT(--(--TEXT(A1:A20,"hh:mm") > =B1),--(--TEXT(A1:A20,"hh:mm") < =B2),--(A1:A20 > =B3),--(A1:A20 < =B4)) did you copy and paste the formula?
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Help Please
2 3
Replies
21
Views
405
Asbestos_Jen
Asbestos_Jen
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
247
dshtesy
D
R
  • Solved
Age Group Ranging by gender
Replies
2
Views
231
Raj1979
R
S
  • Solved
Count of active employees between dates.
Replies
3
Views
631
Fluff
Fluff
N
  • Solved
Formula For Entering Loan Repayment On The Same Day For Each Month
Replies
4
Views
400
Dave Patton
D

Forum statistics

Threads
1,223,236
Messages
6,170,912
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top