Excel Timesheet Error
 

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?
--
Bonnie

Excel Timesheet Error
 

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))

"Bonnie" wrote:

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?
--
Bonnie

Excel Timesheet Error
 

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


"daddylonglegs" wrote:

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))

"Bonnie" wrote:

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?
--
Bonnie