Estimate "common" periods based on criteria

  • Hi,


    I have the columns below:
    [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="width: 111"]A1[/TD]
    [TD="width: 111"]B1[/TD]
    [TD="width: 97"]C1[/TD]
    [TD="width: 128"]D1[/TD]
    [TD="width: 128"]E1[/TD]

    [/tr]


    [tr]


    [TD="class: xl65"]down_event_start[/TD]
    [TD="class: xl65"]down_event_end[/TD]
    [TD="class: xl65"]siteid[/TD]
    [TD="class: xl65"]hostname[/TD]
    [TD="class: xl65"]metric[/TD]

    [/tr]


    [tr]


    [TD="class: xl64, align: right"]18/12/2018 10:00[/TD]
    [TD="class: xl64, align: right"]19/12/2018 10:40[/TD]
    [TD="class: xl64"]net-1[/TD]
    [TD="class: xl63"]net-1-ce.net[/TD]
    [TD="class: xl63"]main_availability[/TD]

    [/tr]


    [tr]


    [TD="class: xl64, align: right"]19/12/2018 10:20[/TD]
    [TD="class: xl64, align: right"]19/12/2018 10:50[/TD]
    [TD="class: xl64"]net-1[/TD]
    [TD="class: xl63"]net-1-cpe.net[/TD]
    [TD="class: xl63"]backup_availability[/TD]

    [/tr]


    [tr]


    [TD="class: xl64, align: right"]19/12/2018 10:00[/TD]
    [TD="class: xl64, align: right"]19/12/2018 10:40[/TD]
    [TD="class: xl64"]net-2[/TD]
    [TD="class: xl63"]net-2-ce.net[/TD]
    [TD="class: xl63"]main_availability[/TD]

    [/tr]


    [tr]


    [TD="class: xl64, align: right"]18/12/2018 10:00[/TD]
    [TD="class: xl64, align: right"]19/12/2018 10:40[/TD]
    [TD="class: xl64"]net-3[/TD]
    [TD="class: xl63"]net-3-ce.net[/TD]
    [TD="class: xl63"]main_availability[/TD]

    [/tr]


    [tr]


    [TD="class: xl64, align: right"]19/12/2018 10:00[/TD]
    [TD="class: xl64, align: right"]19/12/2018 10:50[/TD]
    [TD="class: xl64"]net-3[/TD]
    [TD="class: xl63"]net-3-cpe.net[/TD]
    [TD="class: xl63"]backup_availability[/TD]

    [/tr]


    [tr]


    [TD="class: xl64, align: right"]19/12/2018 10:00[/TD]
    [TD="class: xl64, align: right"]19/12/2018 10:40[/TD]
    [TD="class: xl64"]net-4[/TD]
    [TD="class: xl63"]net-4-ce.net[/TD]
    [TD="class: xl63"]main_availability[/TD]

    [/tr]


    [tr]


    [TD="class: xl64, align: right"]20/12/2018 10:25[/TD]
    [TD="class: xl64, align: right"]20/12/2018 10:55[/TD]
    [TD="class: xl64"]net-1[/TD]
    [TD="class: xl63"]net-1-ce.net[/TD]
    [TD="class: xl63"]main_availability[/TD]

    [/tr]


    [tr]


    [TD="class: xl64, align: right"]20/12/2018 10:00[/TD]
    [TD="class: xl64, align: right"]20/12/2018 10:40[/TD]
    [TD="class: xl64"]net-1[/TD]
    [TD="class: xl63"]net-1-cpe.net[/TD]
    [TD="class: xl63"]backup_availability[/TD]

    [/tr]


    [tr]


    [TD="class: xl64, align: right"]20/12/2018 11:00[/TD]
    [TD="class: xl64, align: right"]20/12/2018 11:40[/TD]
    [TD="class: xl64"]net-1[/TD]
    [TD="class: xl63"]net-1-cpe.net[/TD]
    [TD="class: xl63"]backup_availability[/TD]

    [/tr]


    [/TABLE]


    I am trying to estimate the duratuion of events for a siteid (column D) when the metic (column E) takes the values (main_availability and backup_availability) in common time periods.


    For example, in the above data table, the requested result would be
    a) net-1, duration=20 min -> rows 1,2
    b) net-3, duration=40 min -> rows 4,5
    c) net-1, duration=15 min -> 7,8


    for a), main_availability "down event start" starts at 18/12/2018 10:00 and before its ending, the backup_availability "down_event_ start" begins. From that moment the common downtime event begins for siteid net-1 and it lasts until the first "down_event_up" comes.
    So, the common duration will be: 19/12/2018 10:40 (main_availability down_event_start) - 19/12/2018 10:20 (backup_availability down_event_end)


    i don't know how to apply that logic with vba.


    Thanks

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!