Removing lunch hour from Log Sheet. (Removing Specific Hour duration)

Hello!

Been trying to workout a formula that would remove the lunch time duration from the logsheet total off or leave duration. But it's not a standardized -1 hour.

What i mean is:
Lunch hour is from 11:30 to 12:30

Example 1 : Out: 11:00 In: 12:15
So, leave duration should be 30 minutes.

Example 2: Out 11:45 In 12:45
Leave hours should be 15 min.

Example 3: Out 09:00 In: 12:50
Leave hours should be 2 hours and 50 Minutes

I would appreciate the help as I'm still trying to figure out how to correctly make it work.

Hi, welcome to the forum

If it is not a standard 1 hour, then what is it/what could it be?

What you need to understand about dates and times in excel is…

a date is just a number representing the number of days passed since 1/1/900...and then formatted in a way that we recognize as a date. So, for instance, today (Thu 09 Aug 2018) is actually 43321

Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75

Please provide some sample data for us to work within a dummy workbook (desensitised data). Manually mock up your expected results.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Sorry for the delay

Uploaded an example of the end result
Hope it's good to explain

Note that example 1,2,6 is Normal duration. Just to give an idea.

Tried to break it down and making it realize what is the time out & in in multiple columns but it would just not work!!

Such Frustration.

This proposed solution places the lunch start time in cell start time in cell E2 and the lunch end time in cell E3.
The formula that populates D2, which may then be copied down to D7, is:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.