Excel Timesheet Error


B

Bonnie

My subject is not functioning correctly in two cells and I can't figure out
why. Can anyone help? (I think the same error is in both cells.)

I set up the sheet to calculate 8 hours per day, 40 hours a week and the
rest goes into an overtime column. Additionally, time is rounded down to the
nearest 15 minute period. These are the formulas in the Regular Time Worked
column just before and right after the error cells:

This returns 8 hours per day in the subject column and throws the extra 15
minutes worked that day into the overtime column (just what I want)
=IF(SUM(J$9:J11,I12)>=40/24,MAX(0,40/24-SUM(J$9:J11)),FLOOR(MIN(I12,8/24),15/24/60))

This returns 11 hours worked that day in the regular hour column when only
part of that 11 hours should be reflected here and the rest in the overtime
column ...ERROR CELL... What is wrong???!!??
=IF(SUM(J$9:J12,I13)>=40/24,MAX(0,40/24-SUM(J$9:J12)),FLOOR(MIN(I13,8/24),15/24/60))

This returns 00:00 hours in the regular column (because the time for the
week has totaled 40 housr already) and throws all the time worked for that
day in the overtime column (which it is supposed to do)
=IF(SUM(J$9:J13,I14)>=40/24,MAX(0,40/24-SUM(J$9:J13)),FLOOR(MIN(I14,8/24),15/24/60))

Can anyone figure this out?
 
Ad

Advertisements

D

daddylonglegs

Hello Bonnie, I take it you want to limit the amount returned to 8 hours, try
changing to this formula in J10 copied down

=IF(SUM(J$9:J9,I10)>=40/24,MEDIAN(0,8/24,40/24-SUM(J$9:J9)),FLOOR(MIN(I10,8/24),15/24/60))
 
Ad

Advertisements

B

Bonnie

Thank you very much! I changed the formula and it immediately corrected the
problem.
 

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