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
Steve MolandAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Subodh Tiwari (Neeraj)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
Rgonzo1971Commented:
Hi,
pls try
[>=0.041661]h:mm:ss;mm:ss

Open in new window

Regards
1
SteveCost 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
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Rob HensonFinance 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
Steve MolandAuthor 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
Steve MolandAuthor Commented:
Oh yes, I forgot. I have to do math and sort on the time so a text version is useless.
0
Rob HensonFinance 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

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.