Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Excel Online - Custom Number Format

    I am using Excel Online, through OneDrive, to keep track of my daily workouts. This works well, since it is accessible on my laptop as well as my IPad and IPhone. One problem, I record the total time of my workout and it seems to be OK but when I try to sum a group of the workouts, I am not getting correct results. The times, even though they show as 1:04:55, for example, when I look at it in editing mode, it shows. 1:04:55 AM.

    When I try to format it for elapsed time, there is not an option for that and no option for a custom number format. Is there a way around that? Thank you.

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Donebb,

    Instead of entering 1:04:55, try entering your elapsed time as =TIME(1,4,55) then sum them all up.

    HTH,
    Maud

    donebb1.png

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    donebb (2015-08-28)

  4. #3
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,207
    Thanks
    49
    Thanked 989 Times in 919 Posts
    Or change the cell format to [HH]:MM:SS

    cheers, Paul

  5. The Following User Says Thank You to Paul T For This Useful Post:

    donebb (2015-08-28)

  6. #4
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts
    Maud,

    You are correct. Entering it the way you show does seem to put it in the correct format. Now, I just have to figure out how to change that easily for the 344 entries that I have.

    Paul, you are also correct, that format should work. The problem is that Excel Online doesn't allow, as far as I can tell, custom formats to be used, only the 11 formats that it comes with.

    Thank you to both of you.

  7. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Donebb,

    If your values are in a column and you can run vba then this code will convert all the values to the Time formula:

    Place in a standard module:
    Code:
    Public Sub CvrtTime()
    startcell = 1
    stopcell = 3
    For I = startcell To stopcell
        t = range("A" & I).Value
        range("A" & I).Formula = "=Time(" & Hour(t) & ", " & Minute(t) & ", " & Second(t) & ")"
    Next I
    End Sub
    Change the values in the startcell and stopcell code lines (code lines 3 and 4) to match the first and last rows of your values (not the cell with the Sum formula).
    If the values are in a column different from column A then change the "A" in code lines 5 and 6 to "n" where n is the column with the values.

    ex. 01:16:45 will be converted to =Time(1, 16, 45) as will the rest of the column. Then sum the values with the sum formula.

    HTH,
    Maud

Posting Permissions

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