Data Validation with 24:00 times Excel 2007

Hi

I have a spreadsheet which monitors the hours employees work - one of the rules is that an employee must have 12 hours rest between shifts...

I am using the below Data Validation formula in column I to highlight when an employee has less than 12 hours rest and an alert to appear

=OR(AND(I4-B4<0,"24:00"-B4+I4>=0.5),AND(I4-B4>=0,I4-B4>=0.5))

Now this works perfectly with the exception of when an employee finishes at 18:00hrs on a Friday, and starts at 22:00hrs on a Saturday - Excel Calculates the Rest hours as 04:00, when it should be 28:00

I've attached a sample spreadsheet to illustrate...

Is there any way round this?

Thanks in Advance

Excel Forum Help example.xls

Try this formula for validation of I4 cell.

Please Login or Register  to view this content.

Hi

That works great! - however is there a way to keep the I2 and A2 in the formula - when I copied the formula down the colum all cell references changed therefore the data validation won't work except in I4.

My actual spreadsheet has 100 rows and would need the validation entered on every day of the week

Thanks

I completely missed the obvious to add absolute referencing to the formula!

=I4+$I$2-B4-$A$2>=0.5

And it works!! Thanks you for your assistance