hh:mm:s format to mask leading zeros and/or leading zero hours.

[travellerva]

For the life of me I can't see how to set a custom format for time, where leading zeros don't appear. Also values less than one hour should only show mm:s

Typical displayed values I need are:

7 (seconds)
34 (seconds)
3:45 (minutes; seconds)
38:55 (minutes; seconds)
2:43:12 (hours, minutes, seconds)
11:24:56 (hours, minutes, seconds)

I would accept 11:04:35

I thought [h]:mm:s would work, but it doesn't

Many thanks

 

[Scott Huish]

Now these won't be actual times, but if those are entered as whole numbers I think this will display them that way.

[>5959]#0\:00\:00;[>59]0\:00;0

Book1
	A
1	7
2	34
3	3:45
4	38:55
5	2:43:12
6	11:24:56
Sheet1

 

[travellerva]

Thanks, Scott, for your reply. Unfortunately I cannot make your suggestion work. Maybe I should be more clear about what I need.

Value	Display as
0.00009​	8​
0.0005​	43​
0.002​	2:53​
0.015​	21:36​
0.3​	7:12:00​
0.46​	11:02:24​

What custom format code do I use to get these displays in the second column (sorry - I can never figure out how to paste an xl2bb.xlsm on my Mac)

 

[Tetra201]

Maybe [>0.041666]h:m:s;[>0.000694]m:s;s

 

[travellerva]

Thanks. That almost works. It shows the Value 0.46 to be 11:2:24. I prefer 11:02:24. Is that possible?

Also Value 0.3 shows as 7:12:0 as opposed to 7:12:00

All the others show as needed.

 

[Tetra201]

Try [>0.041666]h:mm:ss;[>0.000694]mm:ss;s

 

[travellerva]

Bingo! I get the general idea. I did this and got what I needed in all examples

[>0.041666]h:mm:ss;[>0.000694]m:ss;s

Thanks a lot.

 

[Tetra201]

Glad to have helped.