Calculating Time difference based on a condition

[sonnethg]

I have 3 columns that have date/time on the first column and device status on the 2nd column and calculated Time difference on the third column. I have sample data below.

I have to subtract the first instance of ENABLE DEVICE DATE/TIME from the previous MALF DEVICE DATE/TIME to get the difference. I do not worry about the DISABLE Device date/time at all. I could do this manually but there are about 20,000 records to go through. I don't know if there is a worksheet function that will do this automatically for me.


Date/Time DEVICE STATUS TIME DIFFERENCE
6/7/04 6:59:48 MALF DEVICE
6/7/04 7:32:44 ENABLE DEVICE 0:32:56
6/17/04 12:23:25 MALF DEVICE
6/17/04 12:33:10 DISABLE DEVICE
6/17/04 12:35:37 DISABLE DEVICE
6/17/04 12:35:59 ENABLE DEVICE 0:53:48

Any help will be appreciated.

[swatsp0p]

I have 3 columns that have date/time on the first column and device status on the 2nd column and calculated Time difference on the third column. I have sample data below.

I have to subtract the first instance of ENABLE DEVICE DATE/TIME from the previous MALF DEVICE DATE/TIME to get the difference. I do not worry about the DISABLE Device date/time at all. I could do this manually but there are about 20,000 records to go through. I don't know if there is a worksheet function that will do this automatically for me.


Date/Time DEVICE STATUS TIME DIFFERENCE
6/7/04 6:59:48 MALF DEVICE
6/7/04 7:32:44 ENABLE DEVICE 0:32:56
6/17/04 12:23:25 MALF DEVICE
6/17/04 12:33:10 DISABLE DEVICE
6/17/04 12:35:37 DISABLE DEVICE
6/17/04 12:35:59 ENABLE DEVICE 0:53:48

Any help will be appreciated.

First, I question your math on the time diff for the second Enable. I come up with 0:12:34.

My best suggestion would be to copy your data to a new sheet. Then, set an Auto-Filter on this range, select the Disable Device entries and Delete those rows. Select Show All. You will then be left with only the Malf and Enable entries. From there it is a simple matter to enter a formula to subtract Enable from Malf times. Custom Format the cells as dd:hh:mm:ss.

Hopefully, others will have easier options for you.

Good Luck.

[vezerid]

Making the reasonable assumption that the entries in column A:A are
sorted ascending, you can use the following formula in C2:

=IF(B2="MALF DEVICE", A2-MAX(($A$2:A2)*IF($B$2:B2="ENABLE DEVICE", 1,
0)), "")

This is an array formula, which means you must use Shift+Ctrl+Enter to
enter it. Copy as far down as possible.

HTH

Kostis Vezerides

[swatsp0p]

Kostis: Excellent formula. I think, however you have inverted what the OP was looking for, and that this may be what they need:

=IF(B2="ENABLE DEVICE", A2-MAX(($A$2:A2)*IF($B$2:B2="MALF DEVICE", 1,
0)), "")

Array entered with Ctrl+Shift+Enter and then copied down the range of 20,000+ rows.

Again, format the output cells (C2:C??) as Custom>dd:hh:mm:ss (use the 'dd:' option if you anticipate gaps of 24 hours or more.

Good Luck and thanks again Kostis.

[sonnethg]

Thanks Kostis and swatsPop. that worked nicely.