• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 129
  • Last Modified:

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
1
Steve Moland
Asked:
Steve Moland
5 Solutions
 
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
 
SteveCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now