Office & productivity software forum: Excel Timesheet formula ~In/Out for Lunch~

If I understand you properly, you are trying to set up a formula that will calculate how many hours you have worked during the day, "less" the time you spent out to lunch. In which case the formula needs an "out/in" function, not an "in/out" function.

Look at it this way. You have 5 columns, with 5 headers, A5 - B5 - C5 - D5 and E5.

The headers are;

Start time - Begin Lunch - End lunch - End time - Total worked.

In A7 is 08:00
In B7 is 12:00
In C7 is 13:00
In D7 is 17:00
In E7 is the result of the calculation giving you the time you worked, "less" the time you spent at lunch.

In the formula you gave, (C7<B7) always fails because C7 can never be smaller than B7. You cannot end your lunch before you begin it.

Also, the * 24 does not give any meaningful answer. It multiplies any result by 24, but what you want, (if I understand this correctly), is how many hours you worked during the day.

So, you need to take the End time and subtract the Start time, then from the result, take away the time spent at lunch, (which will be the End lunch minus the Begin lunch).

ie, you need D7-A7, gives total time at work, less (C7-B7), gives the time you worked, less your lunch time.

eg E7 =(D7-A7-(C7-B7))

Is this what you mean?

Incidently, each cell needs formatting, under the Numbers tab, to "Number" with 2 decimal places.

Mark

I take an empty spreadsheet. Then I fill in

A1: 08:00
B1: 17:00
C1: 0:30
D1: =B1-A1-C1 automatically becomes 8:30

Excel XP does automatic formatting to h:mm (using the Dutch version it's u:mm, but I assume hours are h in English). Anyway, it's automatic, so you can have a look what it should be.

There might be complications with a midnight lunch, but that's not a probable thing to happen.

Hope this helps.


Kees

Hello Mike,
Instead of writing your times for example 0800 for 8 o'clock write it this way 08:00 so that Excel recognizes it as a time value, otherwise it interprets it as just a numerical value and you will just have computation errors.

Now your formula for E7 should look like this :
=((C7-B7+(C7<B7))-D7)*24

Hope this is what you meant, otherwise tell me.

Swisse

and thanks for posting back.

Mark