Time difference calculation question
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(AB11AA12,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! |
Time difference calculation question
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 -- Biff Microsoft Excel MVP "Bob Wall" wrote in message ... 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(AB11AA12,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! |
All times are GMT +1. The time now is 05:52 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com