Reg:macro to find the downtime correlation.

Dear all

Note Excel2016

I would like to develop a VBA macro to obtain the duration of a server outage that occurs when all servers are down simultaneously

The server's uptime and downtime are displayed in the input table. The intended output of the macro can be found in the output table.

Please assist me.

Thanks in advance for your kind support

isolation.png

Regards
Anavarathan S

Here is a solution with a single-cell formula for the whole table in one go (no copy needed):

Please empty all expected results and try:

Please Login or Register  to view this content.

Thanks for the feedback and rep. I was happy to help you with a solution suitable for Office 365. .

Unfortunately, this formula cannot be converted to a formula suitable for Excel 2016.
This formula contains too many new features that can only be used in 365.

I'm not familiar with VBA, but I'm sure a VBA solution is quite possible.

I look forward to responses from VBA experts on this.

I didn't try to reverse engineer HansDouwe's solution, though I expect it does substantially the same thing. I also haven't tried to program it into VBA, as I want to be sure that you are satisfied with the algorithm before going to the trouble of testing VBA code (and because VBA may not be the best programming language for this).

Here's what I did, as I said, just to fix the algorithm/procedure.

1) Unpivot the input table so that I have a list "database" with a timestamp field, a server field, and a "state" field that indicates whether the timestamp corresponds to an "up" event or a "down" event. VBA does not have a built in "unpivot" command, but Power Query/Get and Transform does. If you are not required to use VBA for this, you might consider using Get and Transform/Power Query for this step. I don't have access to Power Query, so I just manually copied and pasted things (again, just to get the procedure down for now).
2) Sort the list by time stamp from oldest to newest. I don't know all of what Power Query can do, but I would be highly surprised if Power Query doesn't include a build in sort command (again, VBA doesn't really have a sort command).
3) Once I have the list sorted by timestamp from oldest to newest, then I go through each timestamp and identify the state of each server at each timestamp. I used simple worksheet functions for this step, but VBA would also be able to do this. Because steps 1 and 2 are probably easiest in Power Query, it might be best if Power Query could do this step as well (I don't know if PQ is capable, but would be optimistic that it could be done).
4) Once I know the state of each server at each timestamp, I can capture/filter the list for those timestamps where all three servers are "down," and extract that timestamp and the following timestamp. I used the worksheets built in filter functions. VBA can use the worksheets filter functions, but does not have its own filter command. PQ, has its own filter commands. If you decide to use PQ for steps 1 to 3, it would be relatively simple for PQ to do this step as well.
5) Once the desired timestamps are extracted, it is a simple subtraction step (as you show in your sample worksheet) to get the duration. All three programming languages (worksheet function, VBA, PQ) should have no trouble with this step.

At this point, you need to decide if you must use VBA for this, or if you are allowed to use any programming language that will get the job done. Then examine your data to see if there are any complicating issues that are not represented in your sample file. Then we can work on programming the solution.

Originally Posted by anavarathan

Thanks for your kind support

is it possible to change this formula to work in excel 2016 or could please provide me vba script that can work in excel 2016.

sorry for inconveience i am unable to change the excel verision in this forum.

Please Login or Register  to view this content.

Can you please help me to get same with IP address instead of server name.

new.xlsx