time difference - text cells ?

[Lukael]

Hello everybody,

I have text cells starting from A1, which are meant to be a time interval for working hours. They are in this format, example: 07:00-19:00 (meaning 12 hours long shift). I want to calculate time difference between cell A1 and B1 (and so on), and highlight cell if time difference between them is smaller than 11 hours. I have this formula, It works fine in the beginning of the row, but makes mistakes when It comes to end of row.

here It is:

Please Login or Register  to view this content.

I'm assuming that happens because formula in last cell calculates from next cell, which is an empty cell.

I need that for monthly worksheet (hours are within one day), so It would be best If formula somehow stops calculating at the end of last cell. Any suggestions ?

Sample worksheet attached, my CF formula is allready inserted !

Thanks for help !

[FDibbins]

Maybe...
=if(A2="","",IF(ISERROR(MOD(LEFT(B1;5)-RIGHT(A1;5);1)*24);0;MOD(LEFT(B1;5)-RIGHT(A1;5);1)*24)<11)

Adjust the A2 to where the blank at the end would be?

[Lukael]

Hi FDibbins, how could I adjust A2, not sure what you mean ?

I forgot to mention that there could be blank cells between time intervals also, not only at the end like in sample worksheet...