Finding the beginning and end of an event in a series of data

Hello all,

I have a table as follows:

Column A: Time of day increasing by seconds (i.e. A2=12:00:01, A3=12:00:02, A4=12:00:03 etc.)
Column B: a statement about an event, for example for a light switch. It is either ON or OFF for periods of time. So cells Bx to By will all be "OFF" where as cells By+1 to Bz will all be "ON" and then again OFF and ON. However, the duration of the OFF and ON events changes every time, i.e. they do not last the same, they vary every single time.

What I'm looking to do:
I'm looking to calculate the duration of the "ON" events. I want to find out how long the light switch was ON, each time it was turned ON (not the aggregate total, but for each event).

Any ideas how I could go about doing this? I can't think of a formula, and the result would look something like this (I guess):

Column X // Column Y (Start) // Column Z (end)
ON // time // time

Perhaps a pivot table. I think I may need to convert the ON and OFFs into numerics (say 1 and 0) and somehow use a pivottable but I can't exactly figure out how.

Any ideas will be much appreciated.

Attachment 155881

Will this do?

Hi, is it possible to post that file in excel 2003 version please?

Resaved as excel 2003...

Attachment 156375