Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Special Formatting For Elasped Time (03)

    Under "Custom Formats" of a cell one can select [h]:mm where the brackets around the letter h will insure that times over 24 hours are reported as hours instead of days. Is it possible to include the text "Hours" and "Minutes" as part of the formatting.

    Example:
    Cell A1 = 7/23/2006 11:51:00 AM
    Cell A2 = 7/22/2006 8:01:38 AM

    Cell A4 = MAX(A1:A2)-MIN(A1:A2) format Cell A4 to [h]:mm

    The result in Cell A4 should be 27:49

    I would like to display it as 27 Hours 49 Minutes

    Thanks for your assistance,
    John

  2. #2
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Special Formatting For Elasped Time (03)

    It took me a while to search for it. See this <!post=post,528943>post<!/post>

    John

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Special Formatting For Elasped Time (03)

    Custom format:
    [h]" Hours "m" Minutes"

    Steve

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Special Formatting For Elasped Time (03)

    Steve,

    I understand the custom format approach but my displayed amount is not what I expect.

    Sub TestFormat()
    Set af = Application.WorksheetFunction

    MsgBox Format(af.Max(Range("A1:A2")) - af.Min(Range("A1:A2")), "[h]""" & "Hours and " & """mm""" & " Minutes")

    End Sub

    I am being returned 12 minutes as opposed to 27 hours and 49 minutes.

    When I test each independantly, I get the correct value for the MAX , MIN and difference. It is when I apply the formatting to the difference the displayed amount is incorrect.

    John

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

    Re: Special Formatting For Elasped Time (03)

    [h] is valid in a custom cell format, but not in the VBA function Format. Try the worksheet function TEXT instead:

    MsgBox af.Text(af.Max(Range("A1:A2")) - af.Min(Range("A1:A2")), "[h]"" Hours and ""mm"" Minutes""")

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Special Formatting For Elasped Time (03)

    Thank you,
    John

Posting Permissions

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