Timesheet Auto Calculations-Need help with correct calculations

Good morning!

I am struggling with a Timesheet format for the office.

We have two different scenarios. We have salaried people, then we have hourly. I have two different timesheets for each because I cannot figure out how to integrate both into one.

But for today, i'd like help with the Salaried People. I've attached the timesheet that I have. Ok, so for all our employees we are allowed a unpaid 1/2 lunch and paid two 15 minute breaks. The timesheet i've attached we enter in just the 1/2 hour lunch so it deducts from our Regular Hours Worked.

Total Day Hours: these are the total hours that the employee is in the office. In this example 7:00-16:30 = 9.5 hours.
Regular Hours:these are the hours worked -after lunch time is deducted. In this example 9 hours worked. (The Vacation, Sick, Holiday, OT, DT - If you enter in a value it deducts from the Reglar hours)

My issue is that the Total Day Hours are coming out to the Regular Hours amount, but it should be .50 more. This is because I have the calculation deducting these. I thought I could just remove that cell from the calc, however if this employee decided to take an extended lunch break, say 12:00-13:30 (1.5) it's not being reflected in the Total Day Hours -Regular Hours will look right, but not Total Day Hours.

Is there a way to get those Total Day Hours to be true to the time the employee is in the office before lunch period?

Thank you in advance!!!

Originally Posted by JohnTopley

Try

in D30

Formula:

Please Login or Register  to view this content.

That does work nicely for the Regular Day hours- but now let me throw a wrench into this.
Regular Hours- how can I calculate this correctly? I have some employees that work 45 hours, some 47.5. With the above formulat on D30 - if I enter in 6:30-16:30 with lunch 11:30-12:00- I get Total Day Hours of 10 and Regular Hours of 9-- That Regular Day Hours should be in this case 9.5 This is probably because the calculation on D31 is incorrect. I'm not great with figuring these out. Trying. Can you please look at that and let me know what I can do differently there?

Thanks again!