MS Excel: How to format cells to show time with minutes and seconds but the hours only when needed?

I have a column of times and want to show time like this:

1:23:45

with hours, minutes and seconds but if the time is less that one hour, then this way:

23:45

without preceding zero or colon.

Not this: 0:23.45

These formats didn't give results I wanted:

mm:ss;@

[h]:mm:ss

Update:

Thanks for your efforts, friends. I appreciate your work.

The cells contain formulas and are read by other cells as time, so they must stay as times.

I suppose I'll just live with the preceding zero and colon. Because of your labor, at least I won't have to continue searching for a format. Thanks again.

2 Answers

Relevance
  • opurt
    Lv 7
    1 year ago

    You can't do this with just cell formats, but you can use a formula to generate what you want. Try this with a time in cell B1:

    =IF(HOUR(B1)=0, TEXT(B1,"mm:ss"), TEXT(B1,"[h]:mm:ss"))

    It returns just the minutes and seconds if there's no hours, otherwise it's elapsed hours (so it can go higher than 24) and minutes and seconds.

    Edit: You can always hide the column that has the raw time in it, and points other cells at it for those that need to work on them as values, while showing the one with this formula in it so that you see it the way you want. If you need to type in or edit the times directly then there's not much you can do -- my formula uses a second column, and the VBA in the other answer changes the time to text.

    • ...Show all comments
    • opurt
      Lv 7
      1 year agoReport

      You're right, I misread what the code was doing. See my comments to your answer.

    • Commenter avatarLog in to reply to the answers
  • 1 year ago

    Edit: opurt is on the right track, but it does not convert the value in the target cell. Here is a VBA event handler that will place the adjusted time in the same cell. This example assumes your times are in column A. If your Time column is not column A, change

    Target.Column = 1

    to

    Target.Column = 2 (for column B up to 26 for column Z)

    Then copy this event handler to the clipboard:

    Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 1 Then

    If Hour(Target) = 0 Then

    Target.Value = Application.Text(Target, "mm:ss")

    Else

    Target.Value = Application.Text(Target, "[h]:mm:ss")

    End If

    End If

    End Sub

    Select your worksheet and right click the sheet tab at the bottom.

    Select 'View Code'

    Paste the event handler into the white editing area to the right.

    Close the VBE.

    Save the workbook as an Excel Macro-Enabled Workbook to retain the functionality.

    This, essentially, emulates the custom format that you desire.

    • ...Show all comments
    • opurt
      Lv 7
      1 year agoReport

      Nice bit of code BTW, never thought of dynamically applying cell formats that way.

    • Commenter avatarLog in to reply to the answers
Still have questions? Get answers by asking now.