How can I find a string of text in lower cells and calculate the difference of time in which they are entered?

[franswa3434]

Hi everyone,

I need to find a way to search the cells below for the next time it appears, and then calculate the difference in time between the two. For example, I need to search the cells below A2 for the next time that same text appears, and then in C2, calculate the difference in time from the next entry. So, A2 needs to search for the next time PON111 is found, then calculate the difference in time. The end result would look like the table below.

Thank you for your help!

Order = Column A
Timestamp = Column B
Hours = Column C

Order</SPAN>	Timestamp</SPAN>	Hours</SPAN>
PON111</SPAN>	11/5/2015 8:00</SPAN>	6</SPAN>
PON111</SPAN>	11/5/2015 14:00</SPAN>	19</SPAN>
PON222</SPAN>	11/5/2015 14:30</SPAN>	1</SPAN>
PON222</SPAN>	11/5/2015 15:30</SPAN>	0</SPAN>
PON111</SPAN>	11/6/2016 9:00</SPAN>	26</SPAN>
PON333</SPAN>	11/6/2015 10:00 </SPAN>	26.5</SPAN>
PON111</SPAN>	11/7/2015 11:00</SPAN>	0</SPAN>
PON333</SPAN>	11/7/2015 12:30</SPAN>	0</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>

 

[Eric W]

Paste this formula in C2:

=IFERROR((INDEX(B3:$B$9,MATCH(A2,A3:$A$9,0))-B2)*24,0)

Change $B$9 and $A$9 to the bottom rows of your data range.

Copy cell C2 and paste it down the column. Make sure you have column C with a General number format.

Let me know how that works. (I assumed the 2016 was a typo.)

 

[franswa3434]

This worked perfectly! Thank you Eric for your help!

 

[Eric W]

Happy to help!