Sum of Overlapping DateTimes in Excel

问题:

I need to determine the total down time of my applications in excel - I only receive alerts of when they went up and down. My goal here is to notify my client of the total downtime, excluding overlaps in time, that there was an outage. For example, Application A was down from 1:30 to 2:30 and Application B was down from 2:00 – 2:30 then the total downtime would be 1 hour not 1.5 hours.

Can someone help as I need an excel formula to do it? My raw uptime/downtime log is below. Thanks!

                    Outage Start    Outage End
Application A   2/7/2014 5:32   2/7/2014 5:37
Application A   2/7/2014 5:22   2/7/2014 5:27
Application A   2/7/2014 5:12   2/7/2014 5:17
Application A   2/7/2014 4:57   2/7/2014 5:07
Application A   2/7/2014 4:07   2/7/2014 4:52

Application B   2/7/2014 7:48   2/7/2014 7:49
Application B   2/7/2014 7:05   2/7/2014 7:06
Application B   2/7/2014 5:31   2/7/2014 5:34
Application B   2/7/2014 5:29   2/7/2014 5:30
Application B   2/7/2014 5:22   2/7/2014 5:23
Application B   2/7/2014 5:09   2/7/2014 5:21
Application B   2/7/2014 4:54   2/7/2014 5:05
Application B   2/7/2014 4:28   2/7/2014 4:49
Application B   2/7/2014 4:06   2/7/2014 4:23

回答1:

I won't be proposing any formulas here, as matters of overlapping time are always something I find confusing to write a formula for. In matters like this, I'd be more comfortable coding something together in VBA. However, as I work a lot with time-based data, I can give you a couple of tips.

The reason why this seems difficult is because you're looking at it in off-on pairs. Don't. This is key to understanding it. Look at it as a continuous flow of time (points for Zen talk), and mark each start and end of the activity accordingly.

In a nutshell, what you're trying to do is simple: find the lowest outage start time in any of the two applications, look for the latest outage end time in any of the two applications that's before the next lowest outage start time greater than the previous outage end time in any of the two applications, and put in all the starts and ends in between for both the two applications. Whew.

The above might seem complicated, but it's not. For example, the earliest O.Start you have is at 4:06 in B. Compared to A, B is earlier by a minute. However, A has a higher O.End with 4:52. Instead of looking at it as overlaps, ignore the idea that they overlap to not confuse yourself and just get the difference between the two. This is because you're billing them for a logical OR-kind type of outage. (meaning if A or B goes down, the bill runs until both comes up).

The way I see it is simple: consolidate all the times in one long list, each marked properly as start and end. Then we look for pairs of a starts and ends. If after a start, you find an end immediately, that's a pair. If start is not followed by a start, count how many consecutive starts you have and find that many ends. The last of those ends will be the final end for that block.

Sounds confusing? Kind of, but not really. See my screenshots below:

This is my sorted data, marked properly.

I then combine the two lists into one, and sort it ascendingly.

Here's the fun part. Mark the first start in the block as red, then find the very next end. If there's a start in between the first start and the first end, "add one" to the end to find. Repeat this cycle until you have no more ends to find and mark that as green.

Now, the very next start is the start of the new block. Repeat the actions in Step 3 above until you finish the list.

Delete the unmarked cells, and just add the differences. For this purpose, I just used the formula in the screenshot and dragged down. Sum is at the bottom.

As I said, I can't provide the formula for you but either by formula or by VBA, this is the logic I'll employ.

Let us know if this helps.

回答2:

Here is a better answer which will more accurately calculate your outage time.

First, compact your data so that all the rows are contiguous.

Create the following formula next to your values (assumes your data starts in A1):

=SUMPRODUCT(--(B3>$B$2:$B$15),--(C3<$C$2:$C$15))=0

This will determine if any of the rows are completely overlapped. Apply a filter over the range and filter out the 'FALSE' values and then sort by outage start in ascending order (oldest to newest).

Then in the next column over use:

=IF(C4>B5,B4,0)

This will determine if time is the earliest start time in the outage block.

=COUNTIF($E$4:E4,"<>0")

Will mark sequentially which block is available to find the max outage end time.

{=MAX(IF(F4=$F$4:$F$15,$C$4:$C$15,""))}

Enter the formula above as an array formula in the next column without the curly brackets (enter the formula and press CTRL + SHIFT + ENTER instead of just ENTER)

Finally, in the next column, use the following formula to find the total outage time.

=SUMPRODUCT(G4:G15-E4:E15,--(E4:E15>0))