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

[excelnewbie34]

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.

04/04/2014 22:51</SPAN>

04/04/2014 22:55</SPAN>

04/04/2014 23:21</SPAN>

05/03/2014 10:29</SPAN>

05/03/2014 10:52</SPAN>

05/03/2014 11:26</SPAN>

05/03/2014 14:35</SPAN>

05/03/2014 14:52</SPAN>

05/04/2014 10:03</SPAN>

05/04/2014 10:43</SPAN>

05/04/2014 16:39</SPAN>

05/04/2014 20:55</SPAN>

05/04/2014 21:28</SPAN>

05/04/2014 21:59</SPAN>

06/03/2014 09:41</SPAN>

06/03/2014 15:37</SPAN>

06/03/2014 17:35</SPAN>

06/04/2014 11:25</SPAN>

06/04/2014 12:44</SPAN>

06/04/2014 14:09 Thank you. You are doing a great job by running this forum. Thanks you.</SPAN>

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

 

[Weazel]

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

	A	B	C	D	E	F
1	Dates and Times		From	To	In	Out
2	3/5/14 10:29		3/1/2014	3/31/2014	9:00	17:00
3	3/5/14 10:52
4	3/5/14 11:26			7
5	3/5/14 14:35
6	3/5/14 14:52
7	3/6/14 9:41
8	3/6/14 15:37
9	3/6/14 17:35
10	4/4/14 22:51
11	4/4/14 22:55
12	4/4/14 23:21
13	4/5/14 10:03
14	4/5/14 10:43
15	4/5/14 16:39
16	4/5/14 20:55
17	4/5/14 21:28
18	4/5/14 21:59
19	4/6/14 11:25
20	4/6/14 12:44
21	4/6/14 14:09

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula D4 =SUMPRODUCT((INT(A2:A21)>=C2)*(INT(A2:A21)<=D2)*(MOD(A2:A21,1)>=E2)*(MOD(A2:A21,1)<=F2)) <tbody> </tbody>

<tbody>
</tbody>

 

[vogel997]

4/4/2014 22:51	09:00		8	=SUMPRODUCT(--(--TEXT(A1:A20,"hh:mm")>=B1),--(--TEXT(A1:A20,"hh:mm")<=B2),--(A1:A20>=B3),--(A1:A20<=B4))
4/4/2014 22:55	17:00
4/4/2014 23:21	1-May
5/3/2014 10:29	31-May
5/3/2014 10:52
5/3/2014 11:26				one more…
5/3/2014 14:35
5/3/2014 14:52
5/4/2014 10:03
5/4/2014 10:43
5/4/2014 16:39
5/4/2014 20:55
5/4/2014 21:28
5/4/2014 21:59
6/3/2014 09:41
6/3/2014 15:37
6/3/2014 17:35
6/4/2014 11:25
6/4/2014 12:44
6/4/2014 14:09

<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> </tbody>

 

[Weazel]

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

 

[excelnewbie34]

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.

 

[vogel997]

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.

'=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?

 

[excelnewbie34]

Hi Vogel,

I copied the formula and changed to suit my sheet.

 

[excelnewbie34]

Will the same formula work for if I want to count between 5 PM and 9 AM? (after I modify the reference cells)

 

[vogel997]

Will the same formula work for if I want to count between 5 PM and 9 AM? (after I modify the reference cells)

=SUMPRODUCT((--(--TEXT(A1:A20,"hh:mm")>=B1)+--(--TEXT(A1:A20,"hh:mm")<=B2)),--(A1:A20>=B3),--(A1:A20<=B4)) Small adjustment

 

[excelnewbie34]

Thank you very much.
Could you please also look at this request?
http://www.mrexcel.com/forum/excel-questions/804881-sorting-exported-data-crystal-report.html

When I export a crystal report some of the product details overflow to next row after column AK.
Is there a way to cut and paste this rows back to their original location.
There are around 1000 rows like this.
Thanks for helping.