Time - again
Posted by Rob on December 31, 2001 12:51 PM
Hi,
I need to calculate how much time is being worked...
b9=1:00 am
c9=10:00 am
d9= total hours worked?????
It is in the time format but i need it to do more that just that... I need to figure out if they work over 5.5 hours then a .5 hour break comes out, if over 7 hours then a 1 hour break comes out.
I tried this formula, but it doesnt work:
=IF((C9-B9)>7,(C9-B9)-1,IF((C9-B9)>5.5,(C9-B9)-0.5,(C9-B9)))
The result of this formula is 9, it should be 8(with the first criteria being true and the formula should subtract 1 from the total?????) Is it because I am subtracting "1" when I should be subtracting 1 hour somehow?
Thanks,
Rob
Posted by Barrie Davidson on December 31, 2001 1:02 PM
See 12323a.html (nt)
Posted by Rob on December 31, 2001 1:12 PM
Re: See 12323a.html (nt)
Barry,
That formula returns a 0:00 result, I am in 'Time' format
Rob
Posted by Scott on December 31, 2001 1:14 PM
Try this formula:
=IF(C9-B9>0.229167,IF(C9-B9>0.291667,(C9-B9)-0.041667,(C9-B9)-0.020833),C9-B9)
by changing 1:00 hrs, 0:30 hrs, 5:30 hrs, and 7:00 hrs to their number formats in the formula, it should work for you. Format the result in [h]:mm
Posted by Scott on December 31, 2001 1:21 PM
Re: See 12323a.html (nt)
If you format this as a number, it will give you a result. Example: 6:30 would equal 6.5
This would be good if you want to calculate of of the result, such as rate of pay: 6.5 hours at $10.00 per hour equals $65.00
Posted by Barrie Davidson on December 31, 2001 1:24 PM
Okay, try this...
=IF((C9-B9)>7/24,(C9-B9)-1/24,IF((C9-B9)>5.5/24,(C9-B9)-0.5/24,(C9-B9)))
Regards,
BarrieBarrie Davidson
Posted by Aladin Akyurek on January 01, 2002 12:23 PM
Re: Okay, try this...
Barry & Rob ---
How about this alternative:
=MOD(C9-B9,1)-VLOOKUP(MOD(C9-B9,1),{0,0;0.229166666666667,0.0208333333333333;0.291666666666667,0.0416666666666667},2)
Aladin : Barry, : That formula returns a 0:00 result, I am in 'Time' format :
