Pulling the start and end of time

New Contributor

Hi everyone! First time actually asking for help in excel. 


So, I would like to end up with a starting time of when a cell contains the value 100 (found in column J) and the end time  when a cell below the starting time contains a 100 as well. For example, I would like L400 to say 00:06:41 (K403), M400 to say 00:07:03 (K425). Then, row 401 would contain the next time that this occurs down in the sequence.


Column I is sequential time, column J is either 0 or 100, column K is =XLOOKUP(100, J400,I400).


I have to go through weeks worth of data soon, so finding a way to calculate this easily is appreciated.




1 Reply
best response confirmed by Grahmfs13 (Microsoft)


I actually figured it out myself if anyone reads this. I used this formula =IF(G2>G3,E2,IF(G2<G3,E2,"")) to tell me if G2->G3 is different than give me the cell in E2, otherwise give me a blank cell.