Timesheet Formula

I need a formula that will sum Log In and Log Out, remove 0.5 hours for lunch, even if less is taken, but also sum any additional lunch time over 0.5. People will be required to take a half an hour lunch, even if they don't take one at all, or take less than 0.5, but we need to subtract from daily hourly totals if they go over.

I'm currently using =SUM((H4-C4)*24)-0.5, but can't figure out how to always subtract 0.5 hours, but also subtract if the user goes over 0.5 hours.

Any help would be greatly appretiated. I haven't worked with an Excel formula in five years.

try this.. it's my version but it should work for you.

Thank you for the replies. I think posting a copy of the file is in order. The timesheet will eventually become rather complex, but for right now, I just want to focus on the Login / out and lunch times, and then work on the other metrics I wish to capture. Let me post the file and clarify exactly what I'm looking for. TimesheetTemplate.xlsx

Please disregard the metrics on the bottom row. I'm not entirely sure which ones we need yet. As noted before, I want to calculate total hours / day, with 0.5 hours taken out, even if less or not taken at all, but also subtract any lunch time over 0.5. Please see the attached file.

~We are currently using a different timesheet from one of our 'Special' associates. It gives me a splitting headache just thinking about it.

Please help and thank you!

No. The latest time would be ~9:00 PM US EST. Sometimes people stay late, but never get close to midnight.

I tried =SUM((H4-C4)*24)-MAX(0.5,(F4-E4)) to no avail.

The output is "FALSE."

And just to clarify, IN, LUNCH and OUT are columns for scheduled times for refference purposes.

It works now. Thank you so much!

I see. Added reputation to Tony Valko, and marking [SOLVED].