How do I get the"Hour" command to stop rounding?

J

Julie Owen

I'm trying to use a modified version of the template "Biweekly Time Sheet"
where the sheet shows the time the employee came in, how long of a lunch was
taken and the time the employee left and the formula should return the total
number of hours when the end time subtracts the beginning time and the result
subtracts the lunch. When I do this using the Hour formula ie.
(=Hour(H15-E15)-F15) in which H15 is the end time, E15 is the beginning time,
and F15 is the lunch time, the amount returned is not correct. It seems like
the formula automatically calculates a 1/2 hour lunch taken when the total
time in the day is 8 hours. It doesn't do this for a 6 hour day--for that
day it returns the correct hour total=6 hours. Also, it seems to round the
number returned. If the total hours worked in the day should be 8.25, it
returns 8.0. How do I get it to stop doing this?
 
B

Bob Phillips

What is in F15? If it is the lunch start time, that will have the effect of
subtracting .5 from you hours attended, which is incorrect if a lunch break
of 45 mins or 1 hour is taken.

=HOUR(H15-E15) works out the decimal number of hours from coming in to
leaving. If F15 is say 1, or .5, it works fine, but if 12:00, it doesn't.

You may want

=(H15-E15)*24-F15

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
J

Julie Owen

H15, for example reads 5:00 PM. E15 would read, for example, 8:30 AM. F15
would read the total amount of the lunch time, if 1/2 hour it would read
".50", if 45 minutes, it would read ".75" and so forth. I tried the below
formula you gave me, but that did not work. It returned a value of 191.5
 
B

Bob Phillips

I get 7.75 for those values using that formula

Sounds like you don't have time in those cells.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top