Time difference calculation question

[Bob Wall]

I have two columns for employees to enter start and end times, If there is
less than 10 hours off from the previous off duty time, the next on duty
time flags in red using conditional formatting. The formula for the CF is
=IF(24*(IF(AB11>AA12,AA12+1-AB11,AA12-AB11))<10,TRUE,FALSE)

The problem is sometimes an employee will have over 24 hours off between end
and start times. If he or she goes off duty at 12:30, and returns the next
day at 12:45, my formula assumes the person only had 15 minutes off and
flags the cell.

Is there a way to change this so the logic will assume that they really had
greater than ten hours off? I've been scratching my head over this for a
while, and I'm sure someone will have a simple explanation.

As always, many thanks in advance!

 

[T. Valko]

One thing you can do for this is to include the date.

In...............................Out
..................................1/1/2008 12:30 PM
1/2/2008 12:45 PM

This will be a pita, but, you can format the cells to display only the time:

In......................Out
.........................12:30 PM
12:45 PM

This will simplify your CF formula:

=(AA12-AB11)*24<10

Or, use another column for the date (if you don't already):

Date................In...............Out
1/1/2008...........................12:30 PM
1/2/2008.......12:45 PM

Then your CF formula becomes:

=((AA12+Z12)-(AB11+Z11))*24<10