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:
with hours, minutes and seconds but if the time is less that one hour, then this way:
without preceding zero or colon.
Not this: 0:23.45
These formats didn't give results I wanted:
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.
- opurtLv 71 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.
- garbo7441Lv 71 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
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")
Target.Value = Application.Text(Target, "[h]:mm:ss")
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.