Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Hour and Min Modifier (Excel 2003)

    Hi,

    Im putting together a simple spreadsheet for a user to record his adhoc working time. So far all was going well until I got to the part when adding up the total weekly hours. The formula Im using below, is ok, until it reaches the 24hour mark and then bottoms out;

    =TEXT(B7+C7+D7+E7+F7+G7+H7,"dd:hh:mm")

    So when the week starts to exceed 24 hours the formula stops working properly, which is a bit of a pain as the user commonly works 40 hr weeks.

    Im going wrong somewhere with the hour modifier at the end of the formula but not sure how to correct it.

    Any advice will be grately appreciated.

    Cheers.
    Lee

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Hour and Min Modifier (Excel 2003)

    Why do you use the TEXT function? It results in a text value that cannot be used for further calculations. I'd use
    <code>
    =SUM(B7:H7)
    </code>
    and format the cell containing this formula with the custom format [hh]:mm
    The square brackets around hh tell Excel to display times over 24 hours.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Hour and Min Modifier (Excel 2003)

    Hi Hans,

    Many thanks for your response. I had used the TEXT function to see what happens as I couldnt get it to work.

    Ive put in your recommendations and all works a treat.

    Thanks for your help.

    Cheers.
    Lee

Posting Permissions

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