Time formatting hh:mm:ss

[Dallman Ross]

Hi, all,

I have data downloaded from a source that provides the time
as 5-6 digits. E.g., 94423, 132218, etc. I want to show it
as hh:mm:ss. So: 09:44:23, 13:22:18, etc. I've tried various
custom-formatting things but can't get it to come out right.
Can it be done simply with formatting, or do I need to resort
to a formula? Help sincerely appreciated.

By the way, for doing it via formula I found this:

=SUM(MID(A1,{1,3},2)/{24,1440})

I found that here:
http://www.mrexcel.com/archive/Formulas/4699.html

I like it, but the problem with applying it to my case
is (a) my data includes seconds; and (b) my data is not
always 6 digits long: there is not a leading zero in what
I download from the source.

Thanks,
Dallman

 

[Fred Smith]

For a custom format, use:

00\:00\:00

To convert to a real Excel time, use:

=time(int(a1/10000),mod(int(a1/100),100),mod(a1,100))

Regards,
Fred.

 

[Dallman Ross]

In <#[email protected]>, Fred Smith
<[email protected]> spake thusly:

Excellent on both counts! Thank you, Fred.
I didn't see before that ":" needs to be quoted
in the custom format. And that formula's the
cat's meow!

Dallman

--------------------------------------

 

[daddylonglegs]

You can also convert with this formula

=TEXT(A1,"00\:00\:00")+0

format result cell as hh:mm:ss

 

[Dallman Ross]

Very nice!

I don't follow the +0 at the end, though. I guess you
are expecting it to cause some sort of data coersion?
But when I leave the +0 off, the formula seems to work
equally well.

Dallman

 

[Dave Peterson]

Without the +0, the value returned from the formula is a string. It'll look
like a time.

With the +0, you'll see a number. And if you apply the nice number format,
it'll actually be a time.

 

[Dallman Ross]

Ah, I see. Of course. Thank you, Dave.

I do believe that it works because of what the CS folks would call
"type-casting" or "coercion," unless I am mistaken. In any case,
very nice soloution from DaddyLongLegs.

Dallman