Share via

Excel Formula or VBA to find if a certain condition exists within the previous eight hours

Anonymous
2023-03-09T21:55:41+00:00

I am hoping for some assistance in either creating either Conditional Formatting, a formula, or VBA Code that will search a Excel table to see if an event occurred on an identified piece of equipment within the last eight hours. In the table below, I am looking for a way to flag the data in Row 10 as this piece of equipment went into alarm less than three hours from the previous alarm. The criteria should be based on If Equipment Location is repeated AND If the log Time Stamp for the new record is less than eight hours from the previous alarm Log Time Stamp.

I assume that if I go the Conditional Formatting method, I will need a helper column.

A B C D E F G H I J K
1 Date Log Time Stamp Initials Equipment Location Equipment Name Alarm Received Action Time Stamp Action RS/RSL/RFL Names RS/RSL/RFL Response
2 3/6/2023 1:30:44 AMS ADEP-1 ALR-65 276 Remote Reset
3 3/6/2023 22:42:05 AMS ADEP-II ALR-89 63
4 3/7/2023 2:45:14 AMS GDEP ALR-58 212
5 3/7/2023 8:10:00 AMS CSTP ALR-44 55
6 3/7/2023 16:09:31 AMS ADEP I ALR-12 55 Remote Reset
7 3/7/2023 16:31:09 AMS CSTP ALR-99 26 Remote Reset
8 3/7/2023 18:14:19 AMS GDEP ALR-268 238 RS/RSL/RFL On-Call Don
9 3/7/2023 19:04:54 AMS ADEP-II ALR-15 10 Remote Reset
10 3/7/2023 19:21:47 AMS ADEP I ALR-12 22 RS/RSL/RFL On-Call Jeremy
11 3/8/2023 7:09:42 AMS LWAC ALR-51 3209 RS/RSL/RFL On-Call Dan Investigating
Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

HansV 462.6K Reputation points
2023-03-09T22:16:45+00:00

I'd use a helper column: enter the formula =A2+B2 in K2 and fill down.

To determine if a row is flagged, enter this formula in L2 and fill down:

=IF(($K2-MAXIFS($K$1:$K1,$K$1:$K1,"<"&$K2,$D$1:$D1,$D2))<TIME(8,0,0),"Flagged","")

Alternatively, use the formula

=($K2-MAXIFS($K$1:$K1,$K$1:$K1,"<"&$K2,$D$1:$D1,$D2))<TIME(8,0,0)

in a conditional formatting rule of type 'Use a formula to determine which cells to format'.

Was this answer helpful?

1 person found this answer helpful.
0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-03-10T11:21:41+00:00

    Thank you for the very fast response HansV. This worked perfectly for me.

    Was this answer helpful?

    0 comments