System Performance Analysis - Calculating System Down Time Ignoring Overlapping Dates

Hi Everyone,

After banging my head on my desk for a little while trying to figure this out I thought I might ask the internet to save me on this friday afternoon. I have been racking my brain for a few days now.

I am trying to conduct some performance analysis for some assets that I look after.

As part of this performance analysis, I am trying to calculate the total time in which one the assets has been unservicable over its entire life.

The asset has various critical systems which are reported defective with a start date and end date (see table below). If any of these critical systems are defective, the entire system is unserviceable.

When simply calculating the total days from the below table you get a value of 44.41. But what I really want is a lot less than that because I would like to ignore overlapping time periods.

Critical System	Defect Start Time	Defect End Time	Total Days Down
A	17/08/2010 23:11	30/08/2010 8:28	12.39
B	26/08/2010 23:38	31/08/2010 23:43	5.00
C	26/08/2010 23:59	31/08/2010 23:50	4.99
D	27/08/2010 0:06	31/08/2010 23:52	4.99
A	14/09/2010 4:40	17/09/2010 18:24	3.57
B	13/09/2010 2:17	26/09/2010 3:07	13.03
E	31/08/2010 15:35	01/09/2010 02:02	0.44

Where I have run into trouble is how to essentially create and store 'timeframes' (for lack of a better word) and check against them and action appropriately. But I'm probably thinking about it the entirely wrong way. At the moment I cant seem to ignore periods where the system is fully functional.

Any help would be greatly appreciated.

P.S I am open to using normal formula or VBA solutions.

Dave

Thanks!

I will test this afternoon and will let you know how it goes. Thank you for taking the time to answer the question.

Cheers
Dave