Time Sheet Calculations

[Laura V]

I am trying to create a time sheet for our payroll department. However
our company rounds the time worked. For instance if an employee i
scheduled to work 7 am to 5 pm, but doesn't leave until 5:06 p.m. The
the employee would receive 10.1 hours of pay. On the other hand, if th
employee was to leave work at 5:04 pm., then the employee would onl
receive 10 hours of pay.

I am attaching what I have so far for the time sheet. The trouble are
is Colum H, rows 7-13.

Please, anyone I really need help with this. I have been drivin
myself crazy for two weeks!!!

Thanks in advance for any help!
Laura

Attachment filename: brandon - ot.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=69371

 

[Gary Brown]

Laura,
Most of us don't access files for fear of viruses.
That said, I think the RoundDown function is what
you are looking for. The 10.10 and the 10.00 are
using the rounddown function.

07:00 AM 05:06 PM 10.10
07:00 AM 05:04 PM 10.00
=ROUNDDOWN((B2-A2)*24,1)

HTH,
Gary Brown

 

[Laura V]

The round down function does not exactly do what I want it to. I wan
to calculate time, using rounding, but my guidelines are strange. Fo
instance, if an employee works from 1 p.m. and leaves at 5:12 pm, the
would receive 4.2 hours. Here is the table in minutes:

0 - 5 = .0
6 - 11 = .1
12 - 17 = .2
18 - 23 = .3
24 - 29 = .4
30 - 35 = .5
36 - 41 = .6
42 - 47 =.7
48 - 53 = .8
54 - 59 = .9
60 = 1.0

When I used the rounddown function for an employee working (if they ar
late) 8:03 am to 2:00 pm, my answer is 5.9. However, I want the answe
to be 6.0, since the 3 minutes they missed fall in the 0 - 5 minutes
.0.

Any more suggestions - I am really open to anything. Thanks again!!
Laur

 

[MWE]

Could maybe be done with a LOOKUP function?

 

[JE McGimpsey]

Then sounds more like you want

=CEILING(MOD(B2-A2,1)*24,0.1)