How can I categorize time?

[JenTaylor]

Greetings,

Perhaps a similar question has been posted, but I cannot find it...I apologize if I am replicating a question.

I have a spreadsheet that lists incidents by date and time. I need to create a summary table of the data that indicates the number of incidents that occurred between 00:00-02:59, 03:00-05:59, 06:00-0859, etc.

Times are formated to view HH:MM, but are stored as hh:mm:ss AM/PM, so I am not having any luck with typing in simple IF statements. I tried formatting the time to text or numbers, but cannot retain the leading zeros. There must be a way to do this, but I am baffled. Any help would be much appreciated!

Thanks in advance!!

-Jen

 

[jimboy]

Hi - welcome to the board

How about this;

=IF(AND(HOUR(B2)>=0,HOUR(B2)<3),TRUE,FALSE)

 

[gaynard_nelson]

Greetings,

I have a spreadsheet that lists incidents by date and time. I need to create a summary table of the data that indicates the number of incidents that occurred between 00:00-02:59, 03:00-05:59, 06:00-0859, etc.

Times are formated to view HH:MM, but are stored as hh:mm:ss AM/PM, so I am not having any luck with typing in simple IF statements. I tried formatting the time to text or numbers, but cannot retain the leading zeros. There must be a way to do this, but I am baffled. Any help would be much appreciated!

Thanks in advance!!

-Jen

Times are displayed as you indicated but this is formatting. The actual entry is a decimal part of a day. 1.0 is 24 hours, .5 is 12 hours or noon. You must use these fractional parts of a day in your formula i.e

=COUNTIF(A12:A20,"<.5") would count all times before noon.

Post again if this is not clear enough

 

[Brian from Maui]

Jen,

Use a coercer for times in your formula, as shown below. What are you trying to count between times. Care to post a sample of your data and expected results.

Book1
	A	B	C	D
1	1	0:29
Sheet1

 

[JenTaylor]

Thank you all for your responses!

I tried using =COUNTIF(A12:A20,"<0.5") and it seems to work. The only thing I had to add to the formula is a SUM statement to subtract the incidents that have already been counted from the current total...i.e. =(COUNTIF(A12:A20,"<0.5"))-SUM(B2:B6).

I am unclear, however, how this works when the time is 00:00. Is it stored as 0.0 or 1.0? Not many incidents occur right at midnight, but there are over forty in a quick count that I did through less than half of my data.

I cannot post a sample of my data as it is sensitive, but here is an example of my problem: My categories are

00:00-02:59
03:00-05:59
.
.
18:00-20:59
21:00-23:59

If the incidents that occurred at midnight are included in the last category, I have an error of x number of incidents in two categories. Is there a way around this short of actually counting the number of occurrences of 00:00 in my data?

-Jen

 

[Yogi Anand]

Hi Jen:

You may want to try ...

y040114h1a.xls
	A	B	C	D
1	souce		timeFrame	incidents
2	12:43:00AM		00:00-02:59	5
3	1:11:00AM		03:00-05:59	7
4	1:39:00AM		06:00-08:59	4
5	2:07:00AM
6	2:35:00AM
7	3:03:00AM
8	3:31:00AM
9	3:59:00AM
10	4:27:00AM
11	4:55:00AM
12	5:23:00AM
13	5:51:00AM
14	6:19:00AM
15	6:47:00AM
16	7:15:00AM
17	7:43:00AM
Sheet5

The formula in cell C2 is ...

=SUMPRODUCT(($A$2:$A$17>=(--LEFT($C2,4)))*(($A$2:$A$17<=(--RIGHT($C2,4)))))