Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    calculate total hours (2003 or 97sr2)

    Please take a look at the attached worksheet and help me figure out how to calculate the total hours at the bottom. The user said this data is a download he receives every day and he doesn't want to have to reformat it each time. Can we calculate with it in the format he has, or is there a simple way to convert his format... I am totally ignorant when it comes to VB. Thank you!!!

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: calculate total hours (2003 or 97sr2)

    That didn't work. What do I do about the cells where he has the start time as 0:00 and the end time as 0:00.. how will Excel know that's a 24-hour period? I must be missing something in your instructions.

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

    Re: calculate total hours (2003 or 97sr2)

    In the Number tab of Format | Cells..., select the Custom category, and enter [h]:mm in the Type box. the brackets [ and ] allow the display of cumulative time (above 24 hours).

    If the user wants an automatic way to apply this, let him create a macro

    Sub SetFormat()
    Selection.NumberFormat = "[h]:mm"
    End Sub

    in his Personal.xls and assign it to a custom toolbar button and/or keyboard shortcut. Legare Coleman's <!post=Personal.xls Tutorial (All),118382>Personal.xls Tutorial (All)<!/post> explains how to create and use Personal.xls.

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: calculate total hours (2003 or 97sr2)

    <P ID="edit" class=small>(Edited by JohnBF on 31-Mar-04 17:22. )</P>Edited.

    The spreadsheet appears to show that all times to be compared appear on the same day. If any of them cross over midnight, then you have a problem with the data layout. Otherwise the formuals seem to calculate correctly, for example the 2nd, 3rd, & 4th May.

    When the total at the bottom is formatted per Hans instructions, the result is correct for me. Note that the formula only adds rows 100 - 110.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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