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
1	*	*	Time Period	Wed 14 Aug	Thu 15 Aug	Fri 16 Aug
2	8/14/2013 6:51	*	06:00 - 07:00	1	0	0
3	8/14/2013 7:08	*	07:00 - 08:00	3	1	0
4	8/14/2013 7:11	*	08:00 - 09:00	1	1	0
5	8/14/2013 7:24	*	09:00 - 10:00	0	1	0
6	8/14/2013 8:32	*	10:00 - 11:00	0	0	0
7	8/15/2013 7:51	*	*	*	*	*
8	8/15/2013 8:51	*	*	*	*	*
9	8/15/2013 9:30	*	*	*	*	*

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