Adjusted Clock Times

Hello,

I'm having trouble coming up with formulas for adjusted times by rules in my 'Time Clock Log'.

I added helper columns to reduce the formulas. What I'm looking for are in cells G5:J5 please.

I really appreciate the help,
Billy

You have set up data validation on cell G5, so it will not allow a blank to be returned from the formula. If you remove the DV, you will be able to use this formula in G5:

=IF(C5="","",MAX(C5,C$2))

This can be copied across into H5:I5, and then down as required.

Hope this helps.

Pete

Try this in J5:

=IF(F5="","",IF(F5-5/60/24>F$2,F5,F$2))

Hope this helps.

Pete

Thanks Pete that works fine. I have one last request please.

I need to add up the paid times and put that in K5.

I have this formula:

=IF(H5-G5>=6/24,H5-G5-30/1440,IF(I5<>"",(H5-G5)+(J5-I5),(H5-G5)))

It works fine with entries but gives me value errors when blank. The 6 hours is there so if an employee works at least 6 hours they must take a mandatory lunch of 30 minutes or they will be docked that amount.

Can you be certain that an employee will clock out for lunch and then back in again? i.e. might you have someone who clocks in at 8:30am (in G5) then clocks out at 4:40pm (in J5) with D5 and E5 (and thus H5 and I5) both left blank? If so, you need to use J5-G5 rather than H5-G5. Also, instead of subtracting 30 (which represents minutes, but would be taken by Excel to mean days) you should subtract 30/60/24.

I think the rules you expressed in the sheet are a bit faulty - what happens if someone clocks out for lunch at 12:10pm? Your logic would set this to 12:30pm and thus give them an extra 20 minutes of working time.

Hope this helps.

Pete

Yes I agree my logic is wrong there. There are some days that they have to leave the day early and don't take a lunch. As long as it's less than 6 hours they should not be docked the 30 minutes but if 6 hours or longer they must take the lunch break or be docked the 30 minutes.

Can you please revise ?