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 th
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 o
20,000+ rows.

Again, format the output cells (C2:C??) as Custom>dd:hh:mm:ss (use th
'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.