# How to format Excel time without leading zero hours

I can't seem to figure out how to format Excel times to not show "0:" when times are less than  23:59

Since the time are less than and more than an hour I'm using a format of h:mm:ss

The times I have show as:

0:24:35
0:45:23
1:02:34
2:10:45

I would like them to show as:

24:35
45:23
1:02:34
2:10:45

Steve in NH
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Excel & VBA ExpertCommented:
You can do that with the help of conditional formatting. Follow the below steps...

1) Assuming your Time column is column A then select whole column A.
2) Use the formula =AND(A1<>0,ISNUMBER(A1),HOUR(A1)=0) to make a new rule for conditional formatting
3) Click the Format and under Number Tab, click Custom and replace General with mm:ss and click OK to finish.

If the time column is not column A, change the column letter in the formula accordingly.

In the attached, I have applied the conditional formatting in column A.
TimeFormatting.xlsx
1
Commented:
Hi,
pls try
``````[>=0.041661]h:mm:ss;mm:ss
``````
Regards
1
Cost AccountantCommented:
The following custom format seems to work for me:

[<=0.0416666]mm:ss;hh:mm:ss

Hopefully it works for you too :)

ATB
Steve.
1
Finance AnalystCommented:
Another option with conditional formatting, formula based as suggested by Neeraj but with this formula:

=A1<=TIME(0,59,59)

and use Format mm:ss

Then apply a default format of h:mm:ss to the range. The conditional formatting will over-ride the default when the condition is true.
1
Author Commented:
Thank you for the suggestions, but none work. More correctly I can not get any conditional formatting to work doing anything. I'm using Excel 2013.  Getting conditional formatting to work is on the self for the moment as the formatting request is actually just a band-aid for what is a larger problem, which is that the "time" data which comes from sources outside Excel.

I'm a long time Excel user, write lots of applications in VBA, where I was eventually going to be applying the conditional formatting. None of the time data is hand keyed, it comes in the form of .CSV files from folks who time road races for runners.

Here is a sample of a typical .csv file.
NET TIME
36:07
36:11
1:02:21
1:02:42

This is what Excel will do with it if you just open the file using Excel [not use VBA]
NET TIME
36:07:00
36:11:00
1:02:21
1:02:42

When I use VBA using "Open xxx as " I can test that the time is less than 59:59 and force "0:" into a text version of the time. However, the 0: is not how the running industry displays times less than an hour.

BTW, probably doesn't matter but the ultimate end results is for these time to show is in a PDF report posted on line as links. That part is easy.  The time thing in Excel has haunted me for years for lots of reasons.
0
Author Commented:
Oh yes, I forgot. I have to do math and sort on the time so a text version is useless.
0
Finance AnalystCommented:
If you can force the 0: as a prefix,  can you not then force the re-evaluation to convert from text back to time to get the raw value correct and then apply the formatting.
1

Experts Exchange Solution brought to you by