# Excel 2007 : hours calculation

1. ## hours calculation

Good Evening and a Happy New Year to all.

I have a further problem,

The attached sheet is taken from a work book i have put together with some very useful help from members on here. We are now using the book in the office and it works really well.

The book records the hours worked, overtime worked and subtracts an unpaid meal break for 2 category of drivers.

I now wish to expand the sheet a little by adding a "control sheet" to have daily and weekly totals of hours we are to be billed for. This is quite within my very limited expertise, except that column "G" always contains "8:00" as 8 hours is the minimum we pay.

Is there any way i could total up the figures in column "G" only when there is data in another cell, for example, only when there is a name in column "A"?

Any help would be gratefully received

Thanks

Scott41

2. ## Re: hours calculation

'm working overtime myself here.

I do not exactly see where this SUM should go.

Summing can be done in various ways. If you would like to do this by Driver, a filter with subtotals might work, or a simple Pivot table.

If it is straight forward, then a SUMIF() does the trick.

... so please specify what you would like to see as an outcome. My preference would a filter.

3. ## Re: hours calculation

You need to understand TIME and DATE formats.

A date is a decimal value specifying the number of days since 00/01/1900 (dd/mm/yyyy)

Today() = 40180
Now() = 40180.0360732639

The fraction 0.036 is the time ! It's a fraction of 1 (being 24 hours)

One hour later = Now()+1/24
Tomorrow = Today() + 1

The fraction 0<x<1 can be displayed in many (cell formats), But remember that the values of time should be a fraction.

In your WB I saw a (beatifull) formula =(E3>F3)+F3-E3
What this does is to add one (1) if the Finish Time is on the next day.

The fraction is now 1. something, but XL discards the 1 and takes only the fraction. In the Pivot table the one is requird so you cannot display a time value (hh:mm:ss) as it stops at 23:59:59.

You need to display the real fractions.

Hope this helps.

4. ## Re: hours calculation

Originally Posted by rwgrietveld
'm working overtime myself here.

I do not exactly see where this SUM should go.

Summing can be done in various ways. If you would like to do this by Driver, a filter with subtotals might work, or a simple Pivot table.

If it is straight forward, then a SUMIF() does the trick.

... so please specify what you would like to see as an outcome. My preference would a filter.
Rwgrietveld,

The sheet i have uploaded is 1 of 7 (Sunday actually). The rest are remaining days of the week.

The "sum" would appear on sheet 8, with 14 "sums" - 1 for each category of driver for each day, then some further simple manipulation of Data.

The "sum" would literally just be the totals of cells G4:G10 and G12:G65

How would i use SUMIF() ?

Does this clarify?

Scott41

5. ## Re: hours calculation

Scott41, it might be an idea to post a file with say 3 sheets: 2 daily sheets & summary sheet - include some dummy data and outline desired results on summary sheet based on dummy values (calculated manually of course).

Originally Posted by Scott41
Is there any way i could total up the figures in column "G" only when there is data in another cell, for example, only when there is a name in column "A"?
Change your formula in G on the daily sheet such that it becomes:

``Please Login or Register  to view this content.``
this way hours worked is only generated where drivers are assigned - as such your total is simply a SUM of G cells.

Note: in your sample file you have G12 using a 45 min break whereas all other rows use 30 min break - not sure if this is by design or whether in fact G12:G65 should all use 45 min break ?

6. ## Re: hours calculation

Happy New Year DonkeyOte,

Will give that a go when i have taken she who will be obeyed out to spend my money.

I have 2 types of driver

Van drivers take a 30 min lunch break and are deducted this amount, but Truck drivers have to take 45 mins, so this is indeed by design

Scott41

7. ## Re: hours calculation

Originally Posted by Scott41
Van drivers take a 30 min lunch break and are deducted this amount, but Truck drivers have to take 45 mins, so this is indeed by design
My point was more to do with the fact that you have the 45 minute break only in row 12 with rows 13:65 using 30 minute break - seems as though you need to correct the template such that rows 13:65 use 45 minute break also.

(on an aside, please don't quite prior posts in entirety - only quote where really nec. and only those parts of the prior post required to make sense of subsequent response)

(p.s enjoy the day out...someone once said that being married is like any other job, it's just better when you like your boss)

8. ## Re: hours calculation

Ahhhhh. I was obviously checking just how observant you were, and congratulations, you passed!

9. ## Re: hours calculation

The attached wb should better illustrate exactly what i am trying to acheive.

On the "totals" sheet, i would be looking for the following result

CELL C:2 SHOULD BE 27.5
CELL D:2 SHOULD BE 31.5
CELL C:5 SHOULD BE 41.0
CELL D:5 SHOULD BE 38.75

Scott

10. ## Re: hours calculation

If you apply the suggested formula revision to Column G on your daily sheets you should find you get your expected results.

11. ## Re: hours calculation

DonkeyOte, we are not worthy to use the same internet as your good self, as you truly are great

Thank you for sharing your greatness

BTW, it works just great

Thanks

Scott 41

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1