Excel 2007 : 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

'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.

I have reworked your WB.

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.

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 ?

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

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)

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