Hi, all-
Our fire department is trying to determine what percentage of the year they have at least one unit out on a call.
I have a spreadsheet of all calls from 2011. There is a unit field (Unit_ID), a call start time (Dispatch_Time), and an call end time (Clear_Time).
A simple subtraction forumla =(end time - start time)*1440 does give me the number of minutes that a unit was on a call. However, unit times overlap. If one unit was out from 1:00 p.m. to 2:00 p.m., and another unit was out from 1:30 p.m. until 2:30 p.m., then the subtraction forumla would count that as a total of 2 hours; when, in fact, there was only 1.5 hours of time where at least one unit was on a call.
Can Excel determine overlapping times from two date fields?
I have attached one day of data as a sample. Thank you in advance for any direction.
Bookmarks