Time formatting hh:mm:ss
 

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 he
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

Time formatting hh:mm:ss
 

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" <dman@localhost. wrote in message
...
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 he
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

Time formatting hh:mm:ss
 

In , Fred Smith
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

--------------------------------------
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" <dman@localhost. wrote in message
...

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 he
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

Time formatting hh:mm:ss
 

You can also convert with this formula

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

format result cell as hh:mm:ss

"Dallman Ross" wrote:

In , Fred Smith
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

--------------------------------------
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" <dman@localhost. wrote in message
...

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 he
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

Time formatting hh:mm:ss
 

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

--------------------------------------
In ,
daddylonglegs spake thusly:

You can also convert with this formula

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

format result cell as hh:mm:ss

"Dallman Ross" wrote:

In , Fred Smith
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

--------------------------------------
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" <dman@localhost. wrote in message
...

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 he
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

Time formatting hh:mm:ss
 

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 wrote:

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

--------------------------------------
In ,
daddylonglegs spake thusly:

You can also convert with this formula

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

format result cell as hh:mm:ss

"Dallman Ross" wrote:

In , Fred Smith
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

--------------------------------------
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" <dman@localhost. wrote in message
...

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 he
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


--

Dave Peterson

Time formatting hh:mm:ss
 

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


In , Dave Peterson
spake thusly:

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 wrote:

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

--------------------------------------
In ,
daddylonglegs spake thusly:

You can also convert with this formula

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

format result cell as hh:mm:ss