# Record a timestamp at each entry & calculate the time difference from the current depth entry to the previous non consecutive one

Creating a Pipe Tally and wish to record time taken from one entry to the next. Using Excel 2016 to enter data by depth and record a Timestamp at each entry. I then want to calculate the time difference from the current depth entry to the previous. I have 4 Columns. Hole Depth Column, Kelly Down Column, Timestamp Column & a Time Diff. Column. The problem I am trying to solve is calculating the time diff between non consecutive Timestamp rows. Ex..

Pipe Tally Auto Time Stamp: How do I calculate this?
Is there a PREVIOUS Time function that could be utilized here?
I only know how the calculate a time difference using a specified cell. Hence the calculation I am using only calculates from the one cell directly above it. Not two or even Three.

Calculates from one cell to the next only: If all the time stamps in the time stamp column are in ascending order, you can subtract the MAX of all the previous time stamps from the current one to get the time difference.

If the first time stamp is in cell F2, and the time difference are in column G, leave cell G2 empty, and enter the following in cell G3

``````=IF(OR(F3="",MAX(F\$2:F2)=0),"",F3-MAX(F\$2:F2))
``````

and copy this formula to the cells in column F that are below row 3.

Note that this puts a blank next to any time stamp that is blank. If you would rather have some other value, replace the second "" with whatever you want.

• Great solution. But if filled down, this formula will put a time difference next to every cell that has a time stamp. Maybe you meant for it to only be copied into those cells where a time difference is to be displayed? – Bandersnatch Aug 28 '17 at 2:11
• @Bandersnatch I believe the OP wants a time difference next to every cell that has a time stamp and his problem is getting that to work when there is no time stamp in the row above. Do you have a different reading of the question? – Blackwood Aug 28 '17 at 3:31
• Yes. Judging from his example, the time differences are supposed to be in every other row with a time stamp (approximately). His problem is that there can be extra blank rows (as there are between the first two time stamps). You idea of using MAX() is insightful. But I think you need eliminate half of the results. – Bandersnatch Aug 28 '17 at 9:41
• The 3 time differences in the picture are correct and he only needs one more - the difference between the first 2 time stamps. It goes in the cell he has colored yellow. – Bandersnatch Aug 28 '17 at 9:49
• @Bandersnatch You may be right. My assumption is that the cell he coloured yellow is just an example of one of the cells that needs a time difference. We won't know unless we hear from the OP. – Blackwood Aug 28 '17 at 18:12