Timesheet Calculation with Time Rounded

[Bee]

I've reviewed numerous posts on timesheets and cannot find what I need. If
you can help, please do.

Cell G totals daily time logged in (in, out, in, out) from times entered in
cells A thru F. This part I got. I want cell H to display only the hours up
to and including "08:00" --- rounded DOWN to the nearest quarter hour. Any
excess time (over 8 full hours) I want displayed in cell I --- also rounded
DOWN to the nearest quarter hour. Ex: If G displays a total of 09:40 (9
hours 40 mintues), I want cell H to display 08:00 and cell I to display
01:30; if G displays only 06:25, I want H to display 06:15 and cell I to
display 00:00.

(This was even complicated for me to explain let alone know how to make it
happen!)

 

[Pete_UK]

Assume you are doing this on row 10 - put this formula in H10:

=FLOOR(MIN(G10,8/24),15/24/60)

and this in I10:

=IF(G10<8/24,0,FLOOR(G10-8/24,15/24/60))

By way of explanation - 8/24 relates to 8 hours (compared with a 24-hour
day), and 15/24/60 relates to 15 minutes.

If you are on a different row, then change G10 as appropriate.

Hope this helps.

Pete

 

[Bonnie]

I must have done something wrong. My total hours for one example day are
10:30 (10 hours 30 minutes). When I put your formula in the next cell, it
calculated 0:33 (which would read as 33 minutes rather than the 8 full hours
needed, shown as 08:00).

In my initial question I used hypothetical cells; here is what I actually
have:

I9 = totaled hours worked for the day (C9 thru H9 reflect the "in" and "out"
times)
J9 = regular pay hours worked (up to and including 8 full hours ---
reflected as 08:00)
Q9 = paid overtime (hours worked for the day above 8 full hours ---
reflected as 02:30 for two hours and 30 minutes)

All hours are reflected in military time (24-hour clock). The time
reflected in Cell I9 is actual minutes worked (i.e., 10:40); the time
reflected in Cells J9 and Q9 have to be rounded DOWN to the nearest worked
quarter hour (08:00 in J9 and 02:30 in Q9).

I haven't done complicated formulas in years so, if you don't mind, can you
spell it out for me?

 

[David Biddulph]

Look again at the result you've got. My suspicion is that you don't have
0:33 (with a colon), but 0.33 (with a decimal point). 0.33 of a day is 8
hours. If you format the cell not as number or general but as time (for
example as [hh]:mm), then you'll see it as 08:00.

 

[Pete_UK]

If your total hours are in I9, then put this formula in J9:

=FLOOR(MIN(I9,8/24),15/24/60)

to give you a maximum of 8 hours, and this formula in K9:

=IF(I9<8/24,0,FLOOR(I9-8/24,15/24/60))

to give you the remainder, rounded down. Format both cells as
appropriate.

Hope this helps.

Pete

 

[Bonnie]

You are right. Boy do I feel stupid! I could chauk it up to being retired
and blaming the cobwebs in my brain, but ...
--
Thank you, again, for your time and effort in replying to my question.

Bonnie

Look again at the result you've got. My suspicion is that you don't have
0:33 (with a colon), but 0.33 (with a decimal point). 0.33 of a day is 8
hours. If you format the cell not as number or general but as time (for
example as [hh]:mm), then you'll see it as 08:00.
--
David Biddulph

I must have done something wrong. My total hours for one example day are
10:30 (10 hours 30 minutes). When I put your formula in the next cell, it
calculated 0:33 (which would read as 33 minutes rather than the 8 full
hours
needed, shown as 08:00).

In my initial question I used hypothetical cells; here is what I actually
have:

I9 = totaled hours worked for the day (C9 thru H9 reflect the "in" and
"out"
times)
J9 = regular pay hours worked (up to and including 8 full hours ---
reflected as 08:00)
Q9 = paid overtime (hours worked for the day above 8 full hours ---
reflected as 02:30 for two hours and 30 minutes)

All hours are reflected in military time (24-hour clock). The time
reflected in Cell I9 is actual minutes worked (i.e., 10:40); the time
reflected in Cells J9 and Q9 have to be rounded DOWN to the nearest worked
quarter hour (08:00 in J9 and 02:30 in Q9).

I haven't done complicated formulas in years so, if you don't mind, can
you
spell it out for me?

 

[Bonnie]

This is great! Everything works perfectly! However, a co-worker reminded me
that my spreadsheet isn't done yet. I need to limit the total hours worked
in the SUM of Cells J9 through J15 not only to 8 hours per day, but also to
40 hours per week. Any excess of 8 hours per day AND/OR 40 hours per week
needs to be thrown into Cells Q9 thru Q15.

I.e., Sunday (the 1st day listed in the timesheet) I work 8 hours, J9
reflects 08:00; Monday thru Thurs I work 9.5 hours each day, J10 thru J13
reflects 08:00 each and Q10 thru 13 reflects 01:30 each; Friday I work 9.5
hours but these hours put me over 40 hours worked this week, so J14 should
reflect 00:00 and Q14 should reflect 09:30; subsequently Saturday's hours
would also be all overtime.

Can the formula you gave me be altered for, say Wed thru Sat, to reflect any
time over 40 accumulated hours that week as overtime in Column Q?

 

[Pete_UK]

It sounds like you work too much, Bonnie !!

Your Wednesday cells should be on row 12, so try this in J12:

=IF(SUM(J$9:J11,I12)>=40/24,MAX(0,40/24-SUM(J
$9:J11)),FLOOR(MIN(I12,8/24),15/24/60))

and this in Q12 (if that is where you have moved the formula to):

=IF(I12<8/24,0,FLOOR(I12,15/24/60)-J12)

Copy both formulae down to cover Thursday to Saturday cells.

Hope this helps.

Pete

 

[Bonnie]

Would you believe I've been retired for 3.5 years? I occasionally go back to
work for the Government when it needs extra personnel (i.e. in New Orleans
for Katrina and Rita, in Texas for the Secure Border Initiative Project).

The 1st formula you gave me seems to work, but the 2nd one results in an
error code "You have entered too few arguments for this function".

Would it be easier for you if I emailed you directly my worksheet?

I really do appreciate your efforts. I couldn't have gotten this far on my
one in another year!

 

[Pete_UK]

Yes, you can send it to me at:

pashurst <at> auditel.net

(change the obvious).

I don't have XL2007, so make sure it is an .xls file.

If there are any other things you want doing at the same time, just
put some notes in the file.

Pete