Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
subtract the time difference from another time difference | Excel Discussion (Misc queries) | |||
Slight Difference in Calculation Between Spreadsheet and VBA | Excel Discussion (Misc queries) | |||
Question calculation time and Index formula | Excel Worksheet Functions | |||
Time difference calculation | Excel Worksheet Functions | |||
Stupid question -- time calculation | Excel Discussion (Misc queries) |