Timesheet Lunch-No Lunch Options

[ocnbluis]

I have been working on this for a day or two now and having a bit of a difficult time figuring out the correct formula so thought I would finally succumb and ask the experts here...

I am working with the following time sheet/card (attached) and it works great when the person goes to lunch, but when they skip lunch I cant get it to compute correctly. I set the formula back to original state because it was just getting more and more confusing. Additionally I tried to adjust it for working overtime (more than 8 hours in a day) and double time (more than 12 hours in a day and kept getting errors or incorrect results).

Any help will be greatly appreciated and coveted.

[DonkeyOte]

On the off chance I've understood

C11: =SUM(MOD(C8-C7,1),MOD(C10-C9,1))
C12: =MIN("04:00",MAX(0,C11-"08:00"))
C13: =MAX(0,C11-"12:00")
copied across etc...

all of the abvoe to be formatted as hh:mm

[ocnbluis]

Thank you for taking the time to respond and for providing the format. I tried out the code you provided and it is still not working correctly. If I input 9:00am as Time In and 9:00pm (2100) as Time Out and they have no lunch break the results are coming out as Worked = 1.50, Overtime as 0.17 and Time and a Half as 1.00.

Thank you for the attempt, I will continue to research solutions and check back from time to time to see if there may be any additional suggestions, but once again; thank you for your response.

[DonkeyOte]

The first formula won't work correctly...it should be:

C11: =MOD(SUMPRODUCT(C8:C10-C7:C9),1)
above can be copied to E11 etc...

The other formulae will all work correctly, however, note my earlier point re: cell format ... the values are time and should be formatted as such - ie hh:mm
(from your prior post the implication being you are using General format)

[ocnbluis]

Did as you suggested and it worked great... thank you very much. One last question about the time sheet. Is it possible to have it set so if someone does take a lunch it subtracts from their total hours? (Not sure that is going to be the case, just wondering for future reference... And once again I humbly thank you for your wisdom.

[DonkeyOte]

Sorry, was not thinking very well yesterday... perhaps best to (again) modify the formula used in C11 to be:

C11: =MOD(SUM(C10-C7,C9-C8),1)
copied across E11, G11 etc...

[ocnbluis]

Thank you once again for all of your time and assistance and thank you for your courtesy.

[ocnbluis]

DonkeyOte has graciously helped me out tremendously on getting some formulas worked out for a time sheet which returns Total Worked Hours, Overtime Hours and Double Time Hours... After getting everything working my dad explained he also needs to know the Normal Work Hours besides the other three.

I think I am close with: =(C12-C11)-IF(C12-C11>TIME(8,0,0),MOD(C12-C11,TIME(8,0,0)),0)

If anyone can please point out where it is messed up I would greatly appreciate your assistance as much as I do DonkeyOte's. I have attached the worksheetand I apologize for my lack of knowledge as I am just trying to help out my parents with their furniture store and tracking their employee hours.

Sincerely,

Thank you for your help.

[DonkeyOte]

Based on my interpretation

C12: MIN(C11,"08:00")

(ie lesser of hours worked, 8 hours)

[ocnbluis]

It is working great, thank you once again for all of your assistance.