I am work for a fire department and we are trying to do some data analysis from our call information. We have been successful using this data in Excel to answer simple questions, but one question has been vexing us for some time. That's the ability to count how many times per day/month/year/etc we have certain units out of service all at the same time.
Attached is a small amount of data with some items redacted from our raw data dump (which is about 40k lines per year). Column U has a description of which unit is assigned, and columns V and Y have the start and end time of the calls. I'm trying to answer questions like, how many times in a given time period are BAM1, BAM5, BAM6 all unavailable at the same time (meaning that the start and stop times of the calls they are on all would have them all unavailable at the same time).
We know this happens a lot, and there are other sets of units we want to apply this logic to, but I just don't know how to use this data to answer this question. I'm hoping to get a number over a year for my larger data sets and then know many times per day different unit sets are all unavailable.