Occurances within Date & Time Range - COUNTIFS

[Pascal]

Hi & Good Morning,

I have a column (H) containing Date & Time as per the following: -

14/08/2013 06:51
14/08/2013 07:08
14/08/2013 07:11
14/08/2013 07:24
14/08/2013 08:32
15/08/2013 07:51
15/08/2013 08:51
15/08/2013 09:30

I've already got a Formula as follows to Count between Hours: -

=COUNTIFS(H:H,">="&TIME(6,0,0),H:H,"<="&TIME(7,0,0))
=COUNTIFS(H:H,">="&TIME(7,0,0),H:H,"<="&TIME(8,0,0))

My question is how can I adjust the Formula so I take into account the Date?

So I could end up with some thing similar to: -

Time Period | 14th Aug | 15th Aug
06:00 - 07:00 | 1 | 0
07:00 - 08:00 | 3 | 1

Also if the Date could display the Day of Week would be a nice touch.

Many Thanks & Regards

 

[MikeWx]

If your dates across the top are date values, a custom format of ddd d mmm would result in Wed 14 Aug for your first date. See my example below for a formula that will count based on both the date and the time range. Copy it down and across for the other counts. You can substitute $H:$H for a specified range of values in column H, but it will cause a significant delay as the values are calculated. I suggest a range like a few hundred or few thousand rows for column H.Sheet1

*	H	I	J	K	L	M
*	*	Time Period
*	06:00 - 07:00
*	07:00 - 08:00
*	08:00 - 09:00
*	09:00 - 10:00
*	10:00 - 11:00
*	*	*	*	*
*	*	*	*	*
*	*	*	*	*

<colgroup><col style="width: 30px;"><col style="width: 115px;"><col style="width: 64px;"><col style="width: 82px;"><col style="width: 82px;"><col style="width: 76px;"><col style="width: 69px;"></colgroup><tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]

[TD="align: right"]Wed 14 Aug[/TD]
[TD="align: right"]Thu 15 Aug[/TD]
[TD="align: right"]Fri 16 Aug[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]
[TD="align: left"]8/14/2013 6:51[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]
[TD="align: left"]8/14/2013 7:08[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]
[TD="align: left"]8/14/2013 7:11[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]
[TD="align: left"]8/14/2013 7:24[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #CACACA, align: center"]6[/TD]
[TD="align: left"]8/14/2013 8:32[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #CACACA, align: center"]7[/TD]
[TD="align: left"]8/15/2013 7:51[/TD]

[TD="bgcolor: #CACACA, align: center"]8[/TD]
[TD="align: left"]8/15/2013 8:51[/TD]

[TD="bgcolor: #CACACA, align: center"]9[/TD]
[TD="align: left"]8/15/2013 9:30[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula K2 =SUMPRODUCT(--(INT($H$2:$H$20)=K$1),--(HOUR($H$2:$H$20)=HOUR(TIMEVALUE(LEFT($J2,5))))) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4

 

[Pascal]

Hi,

Many thanks for the reply.

Whilst trying various formulas I came to notice a problem with the value for the Date & Time I'm storing, which is resulting in the formulas not returning the expected results.

I've created and separate post http://www.mrexcel.com/forum/excel-...e-not-being-stored-date-time.html#post3548383 for this so others might be able to help.

Thanks & Regards