Sorting by time in a time / date field
Posted by Joe G on January 26, 2001 8:33 PM
I have a unique problem. I am responsible for the statistics of our EMS (Paramedic) system. I have an excel file from our 911 system. I need to devise a way to count the number of calls in which we responded to in "Less than 1 minute", ">1min but <2min", ">2min but <3min", etc...; and sorted by time of day. The response times are kept in one column and are listed in seconds, no problem. The Time of the calls are listed in a date/time fashion; ie "11/01/2000 01:00:54". I can format it to just see the military time in the box - although date remains there, just not seen. I can't figure how to sort by hour without the date and to count the number of calls by the two above criterias. HELP!!
Posted by cpod on January 27, 2001 6:00 AM
To strip the date off:
=yourdatetime - Trunc(yourdatetime)
and format as time
You mention two criteria for grouping and counting the response time. I don't quite understand that but you can count the response time in groups:
=countif(COUNTIF(responsetimerange,"<60")
will give you those responses less than 1 minute.
If I haven't understood this correctly please repost.
Posted by Joe G. on January 28, 2001 7:11 PM
I will try the formula you gave. It looks like it will solve the counting based on response time issue. However, I need to be able to group these times by the time of day also. The goal here is to calculate when our response time is the worst (IE - 2am response time of 4 minutes 90% of the time, as opposed to a response time of 2min 90 % of the time during the hour of 2pm). Thanks to everyone for your assistance!
Posted by Joe G. on January 28, 2001 7:12 PM
I will try the formula you gave. It looks like it will solve the counting based on response time issue. However, I need to be able to group these times by the time of day also. The goal here is to calculate when our response time is the worst (IE - 2am response time of 4 minutes 90% of the time, as opposed to a response time of 2min 90 % of the time during the hour of 2pm). Thanks to everyone for your assistance!
