+ Reply to Thread
Results 1 to 11 of 11

Excel 2007 : hours calculation

  1. #1
    Registered User
    Join Date
    12-17-2009
    Location
    Bolton England
    MS-Off Ver
    Excel 2007
    Posts
    27

    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
    Attached Files Attached Files
    Last edited by scott41; 01-02-2010 at 07:36 AM.

  2. #2
    Valued Forum Contributor rwgrietveld's Avatar
    Join Date
    09-02-2008
    Location
    Netherlands
    MS-Off Ver
    XL 2007 / XL 2010
    Posts
    1,671

    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.
    Attached Files Attached Files
    Looking for great solutions but hate waiting?
    Seach this Forum through Google

    www.Google.com
    (e.g. +multiple +IF site:excelforum.com/excel-general/ )

    www.Google.com
    (e.g. +fill +combobox site:excelforum.com/excel-programming/ )

    Ave,
    Ricardo

  3. #3
    Valued Forum Contributor rwgrietveld's Avatar
    Join Date
    09-02-2008
    Location
    Netherlands
    MS-Off Ver
    XL 2007 / XL 2010
    Posts
    1,671

    Re: hours calculation

    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.

  4. #4
    Registered User
    Join Date
    12-17-2009
    Location
    Bolton England
    MS-Off Ver
    Excel 2007
    Posts
    27

    Re: hours calculation

    Quote Originally Posted by rwgrietveld View Post
    '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. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

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

    Quote 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. #6
    Registered User
    Join Date
    12-17-2009
    Location
    Bolton England
    MS-Off Ver
    Excel 2007
    Posts
    27

    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
    Last edited by DonkeyOte; 01-02-2010 at 06:04 AM. Reason: removed unnec. quote

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: hours calculation

    Quote 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)
    Last edited by DonkeyOte; 01-02-2010 at 06:11 AM.

  8. #8
    Registered User
    Join Date
    12-17-2009
    Location
    Bolton England
    MS-Off Ver
    Excel 2007
    Posts
    27

    Wink Re: hours calculation

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

  9. #9
    Registered User
    Join Date
    12-17-2009
    Location
    Bolton England
    MS-Off Ver
    Excel 2007
    Posts
    27

    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
    Attached Files Attached Files

  10. #10
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    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. #11
    Registered User
    Join Date
    12-17-2009
    Location
    Bolton England
    MS-Off Ver
    Excel 2007
    Posts
    27

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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