Excel sheet - calculating hours worked

[robatwork]

Hi I have been given a sheet that has 5 columns that are set to a custom type entry h:mm AM/PM and a row looks like this:

8:30 AM 12:00 PM 1:00 PM 5:00 PM TOTAL: 7:30

I type in work start, lunch start, lunch stop, home time. It calculates my total hours as 7:30 with this formula:
=(E9<D9)+E9-D9+(G9<F9)+G9-F9
This cell is set to custom [h]:mm

I want to amend this to count that hour for lunch as working time ie. end up with 8:30 for total. But if I come back from lunch at say 12:45 PM, I want the total to read 8:45 to account for the extra 15 mins. I had thought this was simple, but I am struggling.....any ideas?

 

[SkipVought]

Hi,

You didn't say what columns your data is in, but I'd GUESS that it is D:G.

Now your logic does not make sense. You are counting the hour for lunch as working time. THAT is just never done. But if it were, clocking in early after lunch, or not taking lunch at all comprises EXTRA TIME? Counting some of this hour TWICE??? You gotta be kidding!!! Do you actually hae a business case for this?

But heres a solution.
1. define Lunch start and lunch end as named ranges LStart & LEnd

2. Name your column ranges as...
Start1,End1, Start2, End2
[tt]
=End2-Start1+End1-LStart+LEnd-Start2
[/tt]



Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}