Need Help With Locking Formula in Protected Sheet

[jmrlifesafe]

Hello!
I have a timesheet that I have created in Excel. I have this set up so that all the employee has to do is fill in the Start and Stop time for each job. Then the sheet auto calculates the Total Day Hours, and Regular Hours. Then if the employee has any vacation, sick , OT or DT they enter that amount where applicable and it will adjust the Regular Hours.

We've been using this timesheet for years, and the only problem we have had is that occassionally an employee will erase the formula in a cell and then before you know it, the sheet doesn't work anymore. So I decided to use the Locked Cells and Protect the worksheet.

I like how it works, but I have one small issue.

Although we require everyone to take a lunch, there are instances in which they will not. As you will see in my example, this employee was at job1 from 7-12, then went to job2 12-1300, and went to the dr from 1300-1630. This employee did not take a 30min lunch break so that they don't have to take as much of their vacation time.

The problem is, I have the Regular Hours row locked. So they can't adjust this. This employee is supposed to work 9 hour days, so the Regular and Vacation should equal 9. Any suggestions? My only thought is to add another row saying Adjusted hours and leave it unlocked, but I think this will just add to confuse the employees.

Thank you!

[AliGW]

I can't see where in the sheet there would be a problem if the employee did not fill in their lunch break times - could you explain a bit more?

[jmrlifesafe]

The total day hours are 9.5 if they don't put say 12:00-12:30 in the Lunch row. If they do, they total day hours are 9 -this is working just fine.

The Regular hours without the lunch calculates to 8 hours. Which is correct. Then the employee types in the number 1 in the Vacation Row, and the Regulars adjust to 7. That is only 8 and not what they should be paid for. They are to have 9 hours in their day. The dr. appt was 2 hours long but they didn't take a lunch. Therefore the Regular Hours should be 8. I can't figure a way to do this without unprotecting that cell so they can change it manually, and I don't want to do that because they will delete the formula that is there and save and the next time if they had a regular 9 hour day, it would auto calculate for them.

[AliGW]

You could add an IF clause to the formula to allow for a 'missing' lunch break.