Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 9
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
subtract the time difference from another time difference Dannigirl Excel Discussion (Misc queries) 3 September 30th 07 03:47 PM
Slight Difference in Calculation Between Spreadsheet and VBA Clint Schwartz Excel Discussion (Misc queries) 4 January 10th 07 06:48 PM
Question calculation time and Index formula S Davis Excel Worksheet Functions 8 November 8th 06 12:41 AM
Time difference calculation Sasha Excel Worksheet Functions 5 July 22nd 06 05:55 AM
Stupid question -- time calculation Markndawoods Excel Discussion (Misc queries) 4 July 3rd 06 10:25 PM


All times are GMT +1. The time now is 12:28 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"