asked on

How do I count the number of entries between two rows and how do I calculate the time between two start times

I am attaching a file that has two Start entries (C4 and C31). This is just a sample set so this would be repeated for thousands of rows. I want to know how to do two things: how do I count the number of entries between each Start e.g. it should be 26 - but to do that for all the rows in the data set. Secondly how do I show the time elapsed (in hours) between each occurrence of start. (cell T7) - again for each occurrence of this start...basically the idea is that a pump starts...does its thing. Stops and then starts again...
EE--calculating-rows-between-entrie.xlsx

Rob Henson

Does each block of data have some means of identifying it from other blocks?

For example a run reference.

With a reference field, you could identify the lowest time value for that reference for the start time and the highest time value for that refernce for the finish.

Looking at the way you are calculating the difference, your formulas in S6 and S7 can be simply

=Q6 + R6
=Q7 + R7

then formatted as "dd/mm/yy hh:mm". Converting to text isn't necessary as the formula in T7 looks at the components and recognises them as date and time and converts them back again.

agwalsh

ASKER

Each of the entries - including start etc have an unique event ID but is there a way to basically get the formula to re-start the calculation every time it finds a start entry? So that for the example I give you it says 26 but when the next sequence starts and there are 4 entries between the two starts it says...4...

Shums Faruk

For getting the Number of Events between two entries, you can use below Array Formula, confirmed with Cntr+Shift+Enter and drag down

=SMALL(IF($C$2:$C$31=$R$4, MATCH(ROW($C$2:$C$31), ROW($C$2:$C$31)), ""), ROW(C1)+1)-SMALL(IF($C$2:$C$31=$R$4, MATCH(ROW($C$2:$C$31), ROW($C$2:$C$31)), ""), ROW(C1))-1

Open in new window

And for calculating time difference you don't need to combine Date & Time with Text Function, you can just calculate by adding (End Date + End Time) deducting (Start Date + Start Time)
Please find attached for your reference...
EE--calculating-rows-between-entrie.xlsx

Shums Faruk

OK,

I added few data to test, please see attached revised formulation, which will list dates & times as well as per appearance of "Start".

As you have thousands of rows, please change the range accordingly.
EE--calculating-rows-between-entrie.xlsx

SOLUTION

Rob Henson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

agwalsh

ASKER

@Rob Henson - Hm, I was wondering that too...will be intrigued to see what you come up with..
EE_calculating-rows-between-entries.xlsx

ASKER CERTIFIED SOLUTION

Shums Faruk

membership