Excel中重叠的DateTimes之和 [英] Sum of Overlapping DateTimes in Excel

问题描述

我需要在excel中确定我的应用程序的总停机时间-我只会收到有关它们何时停机的警报.我的目标是将总停机时间(不包括时间重叠)通知我的客户发生了停机.例如，应用程序A从1:30下降到2:30，应用程序B从2:00 – 2:30下降，那么总停机时间将是1小时而不是1.5小时.

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.

有人可以帮忙吗，因为我需要 excel公式来做到这一点?我的原始正常运行时间/停机时间日志如下.谢谢！

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

推荐答案

在这里我不会提出任何公式，因为重叠的时间总是让我为编写公式感到困惑.在这种情况下，我会更愿意在VBA中一起编写代码.但是，由于我经常处理基于时间的数据，因此我可以给您一些提示.

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.

之所以看起来如此困难，是因为您正以断断续续的方式看待它.别.这是理解它的关键.将其视为持续的时间流(Zen谈话要点)，并相应地标记活动的每个开始和结束.

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.

上面的内容看起来很复杂，但事实并非如此.例如，最早的O.Start在B的4:06处.与A相比，B早一分钟.但是，A与4:52相比具有更高的O.End.不要将其视为重叠而不是将其视为重叠，而不要混淆自己，而只是获得两者之间的差异.这是因为您要为logical OR类型的中断计费. (这意味着如果A或B下降，则帐单会一直运行，直到两者都出现为止).

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.

现在，下一个起点是新块的起点.重复上述步骤3中的操作，直到完成列表.

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.

正如我所说，我无法为您提供公式，但是无论是通过公式还是通过VBA，这都是我要采用的逻辑.

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.

这篇关于Excel中重叠的DateTimes之和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！