Calculating difference between two times through a column of data

Hi, has anyone come across a problem like this before, I could use your advice?

It will be easier to explain in an example:

A B
Time (hh:mm:ss) Digital
1 10:03:00 0
2 10:03:01 0
3 10:03:02 1
4 10:03:03 1
5 10:03:04 1
6 10:03:05 0
7 10:03:06 0
8 10:03:07 1
9 10:03:08 1
10 10:03:09 0
11 10:03:10 1
etc......

From the data above I am searching for the duration of when the digital column says '1', i.e my function = A5-A3 which would output 2 secs or 00:00:03 & likewise A9-A8.

The problem I have is that the digital signal is staggered and does not always have the same frequency. I have over 6848 lines of time to check so to do this manually would take me all day.

Hopefully I have explained my problem and someone has an idea on how to solve this?

Cheers,
Going_loco

Hi ramananhrm,

I tried your formula and it just seems to output 1, do I need to modify it in any way?

FYI I need to calculate the duration everytime the digital value is 1.

For example in column C I would have a collection of the time differences each time I have a value of 1 and ignore the zeros.

Thanks for your help.

Going_loco

Originally Posted by ramananhrm

I have counted all the '1'.

Please confirm if it is a right resolution.

=SUMPRODUCT(--((RIGHT($A$2:$A$12,1)+0)=1))

Hi XOR LX ,

Good idea, hopefully I attached the spreadsheet correctly.

You'll see in column C I simply subtracted the time when column B outputs 1 from the last time column B says 1 and get a difference in time. I did a couple of the first instance and you'll see straight away that the duration between 1's is staggered and the length of time 1 appears is random.

Actually now that I think of it, it would be helpful to know what the duration of the zeros was too.

Cheers,
Going_loco

Hi XOR LX,

I would like to have your babies.

Thanks a million that worked a charm.

Cheers,
Going_loco

The attached file shows how you can produce a summary table of the durations for zero and one. I've used column D as a helper column (coloured blue) with this formula in D1:

Formula:

Please Login or Register  to view this content.

and this formula in D2:

Formula:

Please Login or Register  to view this content.

which is then copied down to the bottom of your data. The idea is that transitions from 0 to 1 are shown with an increasing number which ends with 0.5, and transitions from 1 to 0 are shown with an increasing integer.

Then I've put some headings in G1:H2 and the number 1 in F3 with this formula in F4:

Formula:

Please Login or Register  to view this content.

This formula is in G3:

Formula:

Please Login or Register  to view this content.

so that if your first value in B2 is a 1 this cell shows blank. A slightly different formula is used in G4:

Formula:

Please Login or Register  to view this content.

Both cells are formatted as hh:mm:ss, and return the time differences for the zero values by looking for the time values between 0 and 0.5 (row 3) and between 1 and 1.5 (row 4). A similar formula is used for the 1's in H3:

Formula:

Please Login or Register  to view this content.

which is copied into H4, with both cells formatted as hh:mm:ss. You need to copy the formulae in F4:H4 down, and to assist you I've put another little formula in I4 to tell you how far, depending on your data.

Hope this helps.

Pete