Calculating time difference between two days combined with IF formula

Hi

I am using the following formula in Column E to calculate the difference between an employees start time, and their previous shift end time in order to work out how many hours rest they have had:

=IF(A4=0,"",IF(I4=0,"",MOD(I4-B4,1)))

My Current Formula works fine for same day calculations, but if an employee finishes work at 18:00 on Friday and starts work at 22:00 Saturday night, Excel calculates the Total Rest hours as 04:00, when in fact they have had 28:00 hours rest...

Is there a way of calculating the hours difference between two dates?


I have attached a sample of my spreadsheet to illustrate

Hi Fotis

Thanks for getting back to me! The formula looks good, but on row 8 it seems to add 1.00? I've attached the spreadsheet
again with some other figures entered - if you look at row 8 the total rest hours calculates to 13.00 instead of 12.00?

Any Suggestions?

Thanks

My Apologies - you are correct - I think I've been starting at this too long now lol!

Thank you so much for sorting for me