Availability analysis

Hello,

I am trying to do some analysis of vehicle availability by hour of the day / day of week for my organisation. The vehicles are not available all the time as they require four trained staff to be working to man them. When the required staff are working the system books the vehicle as 'available'.

I can extract the availability data into Excel as the following columns:

Vehicle reference number
Availability from (date and time)
Availability to (date and time)

We have a number of vehicles listed in one spreadsheet I would like to identify the % that each vehicle is available for each hour of the day / day of week based on one year of data.

I was going to present the information in a matrix with days on the X axis and hours on the Y for each vehicle.

If anyone could give me some advice on the best way to tackle this I would greatly appreciate it.

Pip

I guess this one is a toughy!

Hi MarvinP,

I have included some randomly generated data in the first sheet (this matches the format we have exactly), the second sheet shows an example of the outputs that I would like to achieve (average availability by hour / day, by month and by year).

Any help would be appreciated, it sounds like you like a challenge!!

PipGIS

Hi guys,

Thanks for your efforts.

MarvinP I can see how you have approached it, do those percentages relate to the first hour of availability rather than counting the hours between the 'from' & 'to' columns?

I need to be able to understand where availabilty is low across an average week so the most important output is the days against hours pivot-table (rather than months) as I would like to understand where we need to improve our usage of the vehicles.

PipGIS

Hi guys,

Thanks for your efforts.

MarvinP I can see how you have approached it, do those percentages relate to the first hour of availability rather than counting the hours between the 'from' & 'to' columns?

I need to be able to understand where availabilty is low across an average week so the most important output is the days against hours pivot-table (rather than months) as I would like to understand where we need to improve our usage of the vehicles.

PipGIS