Adding up Hours from two different times, minus lunch hour

I have 2 drop-down time selectors from a named range in 2 adjacent cells. Cell 1 is the start-time, cell 2 is the end-time. Cell 3 needs to be the sum of hours (and 1/2 hour if selected) between the two times. However, if the start-time and the end-time span over 12:00pm to 1:00pm, I need to subtract the lunch hour (which is the standard hour and doesn't change). For example if the start-time is 10:00am and the end-time is 2:00pm, the total hours needs to be 3 hours, not 4 hours. Any help would be greatly appreciated! Thank you!

Hi and welcome to the forum.

I'm not sure what you mean by this bit.

Originally Posted by JCI2112

(and 1/2 hour if selected)

Selected where/how?

Perhaps attaching a sample workbook showing your data layout and expected results in different scenarios would be useful.

BSB

Sample spreadsheet is attached.

Try this in F4:

Formula:

Please Login or Register  to view this content.

You still haven't explained what you meant about the half hour if selected though...

BSB

I just meant that the start-time or end-time could be on the half-hour, not the just the whole hour. eg, 10:30 to 3:30.

Aha, that makes more sense now.

The formula in post #5 should work for you then.

BSB

Works perfect! Thank you!