Automating time difference

[kinkart]

Hello,

I have a day table (see sample file) where one can put an X anywhere on row 4. Based on this, I'd like to automate the time difference between 2 "X"'s on row 5 so that my vlookup on row 6 pulls out the correct value.
I've tried various ways including changing the format to time, but none seems to work correctly, and I'm sure there must a formula combo to make this work. Can anyone put me in the right direction please?

 

[Somendra Misra]

Hi,

Can you explain why do you have 10 in I5?

Regards,

 

[kinkart]

that would be the difference from the previous X (22:00) and this one (8:00). From 10pm (or 22:00) to 8am, there is 10 hours. It's what I can automate.

 

[Somendra Misra]

See the file.

Regards,

 

[kinkart]

wow, that was quick and perfect! Thank you Somendra!

 

[Somendra Misra]

Thanks for the feedback and welcome back anytime!!!


Regards,

 

[Swapnil]

Below is the solution with the help of Array formula ,