Time Calculation help needed

Hello,

I am trying to help my boss out by making our Excel spreadsheet schedule work smarter. Unfortunately, I have to work within a standard schedule provided by the company, so I am hoping someone can help me out, even if it's only with pieces of my request.

Background:
This is for scheduled hours, not actual worked hours (otherwise, this would be easier for me). I have start and end time columns for each person. I have it calculating daily and then weekly hours. However, some days will have text instead of times if they have the day off. The schedule does not account for lunch breaks, so I have that built into the daily hour calculation formulas.

Help needed:
I need the daily calculations to put out a zero if the field includes text or if it is left blank. If it helps, the text will state either "off" or "vacation." Blanks are problematic since I had to build the lunch hour into the calculation formula; they are creating negative hours worked.)
***For example, I need it to say C3-B3=X hours unless B3="off" "vacation" or is left blank, then C3-B3=0

Bonus question:
Can time cells be formatted to display without the am/pm but also without using 24-hour format? The times will be typed directly into the field, not into a formula, so it really needs to be done with cell formatting.

Thanks so much in advance!
Gina

We've lost power due to the storm. I will mock it up and submit as soon as I can. Thanks!

I have attached the mocked up sample with the current formats and formulas as well as what I need it to look like. Thanks!

Originally Posted by dflak

Explain to me a bit more on how the lunch hour is calculated. I see that on Saturday, the lunch hour seems not to be taken out of the calculation. On the other hand time worked Mon-Fri does have a lunch hour taken out. So this seems to indicate that if the start and end times span the lunch hour, then an hour is deducted. What is this time span?

Saturdays do not have a lunch because we close at noon, so it was taken out of the formula for that day.
Mon-Fri, lunch is either half or full hour, depending on how many hours the employee is scheduled for. It typically doesn't vary within one person's schedule, so as long as I have the lunch hour subtracted correctly for each person, it should calculate hours correctly.
I will take a look at your attachments when I get home from work this afternoon.

For lunch, less than 5 hours scheduled is no lunch, 6 hours or more is an hour, and anything in between is half an hour.

To answer your question in the Word doc about the Bonus question since you don't see a difference...
I'd like to know if there is a way to format the cells so that it displays the time without displaying the am/pm but also not displaying in 24 hour time.
For example, if the cell currently says "6:15 PM", can the cell be formatted to display it as "6:15"? Obviously, when typing the time in, it'd still need to be entered as "6:15 PM" for the calculations to work correctly.
If not, that's fine, but that's why I called it a bonus. I would like to keep the readability simple, and not change it too much from what people are used to seeing on the schedule currently.

Thank you so much for your help. I can make this work.