Data Validation with 24:00 times Excel 2007

[KJ2607]

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 say, 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 had a suggestion to use the following:

=I4+I2-B4-A2>=0.5

(I2 being the saturday date, A2 being the Friday date) - this does work but only for Cell I4, If I copy the Data
Validation down the column all cell references change - is there a way to keep the I2 and A2 in the formula without having to retype in every row?

Alternatively can anyone suggest a different solution to my problem?

 

[mole999]

If you select I2 or A2 you can manually change them to $I$2 or $A$2 the dollar sign constrains column or row, these example would constrain it to only A2 or I2, the other way is to highlight the values. you can press Function F4 and it will provide the same changes

if the date is input with the time then it becomes easier to ensure the correct separation, if your are typing live then you can try this

select a cell, hold down CTRL amd press ; then press space bar, then hold down CTRL and SHIFT and press :