Time computation II
Posted by Rob on December 21, 2001 11:42 AM
Didn't work, it came out like this:
7:00 15:00 8:00
23:00 7:00 8:00
23:30 7:00 7:30
0:05 0:15 0:10
0:15 0:30 0:15
23:01 0:07 1:06
2257:01
2257:01 is where it should have come up as 25:01
Any other thoughts?
Posted by Scott on December 21, 2001 11:52 AM
2257:01
Posted by IML on December 21, 2001 11:53 AM
2257:01
Change your subtraction formula
to =B1+(B1<A1)-A1
assuming you won't have any period cross two days.
good luck
Posted by Scott on December 21, 2001 11:54 AM
When formatting, make sure to put the brackets [] around the "h". [h]:mm
This should work. 2257:01
Posted by IML on December 21, 2001 11:54 AM
repost
2257:01
Lets see if this works
=B1+(B1 < A1)-A1
Posted by Aladin Akyurek on December 21, 2001 11:57 AM
and no need for another thread :)
Lets say that the 3rd column times are in C from C1 on:
In D1 enter: =SUM(C1:C6)
Custom format D1 as [h]:mm.
I get: 25:01 in D1 as result.
Thus: try again.
Aladin
========= 2257:01
Posted by Rob on December 21, 2001 12:02 PM
2257:01
My employees work 24/7....so some folks come in at 11pm (23:00) and get off at 7am (7:00)the next day for a total of 8 hours (8:00)
I have the subtraction formula set fine it's ... =IF(C31-B31<0,24-(B31-C31-8),(C31-B31), ... but when I try to total up the third column above, it won't come out right...
Posted by IML on December 21, 2001 12:09 PM
2257:01
I think your formula should be (with start time in B31 and end time in C31
=C31+(C31<B31)-B31
or
enter date and time and simply use =C31-B31
If you change the format to general on say the person working from 23:00 to 7:00, and the answer is not .33333,
this is the cause of your problem.
Posted by IML on December 21, 2001 12:10 PM
Please scroll down in the comments box to see what I actually typed. (nt)
2257:01
Posted by IML on December 21, 2001 12:38 PM
Recap
Excel stores times as fraction of a day
An eight hour shift (8/24) should be .33333
You can account for passing midnight in two ways.
1) Enter the date and time for your start and stop times and use the formula
=C31-b31
or
2) Create a formula that accounts for a passage of midnight if you enter just time. This will work as long as no one works more than a 24 hour shift.
Assuming 23:00 in B31 and 7:00 in C31
Your formula
=IF(C31-B31 < 0,24-(B31-C31-8),(C31-B31))
ends up with 31.33 (ie 31 days and eight hours). When formatted to time, you just see the eight hours, but excel is adding the 31 days.
You can correct this with an if statement of
=IF(C31 < B31,((1-B31)+C31),(C31-B31))
or more simply using boolean
=C31+(C31 < B31)-B31
Posted by Rob on December 21, 2001 12:40 PM
Re: repost
OK, (BTW..thanks for helping me), here is something interesting...
I tried your suggested formula, but got several errors. If I keep my original formula in, I get correct results, except when I try to total up the 3rd column....here's the interesting part...when I type in the numbers of the 3rd column WITHOUT a formula, and then total them up using custom format .... [h]:mm .... then I get 25:01 like I want, but that does me no good because then the times worked each day won't be calculated.
Uhhgg! So frustrating...
Rob
Posted by Rob on December 21, 2001 12:49 PM
Re: Recap
That was it! It works! THANK YOU THANK YOU THANK YOU
Rob
