I am having a little problem here. I am employed for a small television
station, and have created a worksheet to track how many times each commercial
spot is played, and at what time it is played. I need to be able to insert
the times quickly without typing in the colon, and have Excel insert the
colon for me (i.e. 12657 = 1:26:57). I figured that I could format my cell as
follows: "h:mm:ss"
This did not do the trick. When I insert a time without the colon, it
fromats it with a strange date. Anyone know of a custom format that will
work for this problem?

Add this formula to the adjacent cell and form at as hh:mm:ss

=TIME(INT(A1/10000),INT((A1-INT(A1/10000)*10000)/100),MOD(A1,100))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"onlyinsyder" wrote in message
...
I am having a little problem here. I am employed for a small television
station, and have created a worksheet to track how many times each
commercial
spot is played, and at what time it is played. I need to be able to
insert
the times quickly without typing in the colon, and have Excel insert the
colon for me (i.e. 12657 = 1:26:57). I figured that I could format my cell
as
follows: "h:mm:ss"
This did not do the trick. When I insert a time without the colon, it
fromats it with a strange date. Anyone know of a custom format that will
work for this problem?

One more formula in an adjacent cell you could use:
=--TEXT(A1,"00\:00\:00")

If you're interested, you may want to look at how Chip Pearson approached it:
http://cpearson.com/excel/DateTimeEntry.htm


onlyinsyder wrote:

I am having a little problem here. I am employed for a small television
station, and have created a worksheet to track how many times each commercial
spot is played, and at what time it is played. I need to be able to insert
the times quickly without typing in the colon, and have Excel insert the
colon for me (i.e. 12657 = 1:26:57). I figured that I could format my cell as
follows: "h:mm:ss"
This did not do the trick. When I insert a time without the colon, it
fromats it with a strange date. Anyone know of a custom format that will
work for this problem?

--

Dave Peterson

Thanks Dave.
I think I can get by with that.

Brent

"Dave Peterson" wrote:

One more formula in an adjacent cell you could use:
=--TEXT(A1,"00\:00\:00")

If you're interested, you may want to look at how Chip Pearson approached it:
http://cpearson.com/excel/DateTimeEntry.htm


onlyinsyder wrote:

I am having a little problem here. I am employed for a small television
station, and have created a worksheet to track how many times each commercial
spot is played, and at what time it is played. I need to be able to insert
the times quickly without typing in the colon, and have Excel insert the
colon for me (i.e. 12657 = 1:26:57). I figured that I could format my cell as
follows: "h:mm:ss"
This did not do the trick. When I insert a time without the colon, it
fromats it with a strange date. Anyone know of a custom format that will
work for this problem?

--

Dave Peterson

Try this

Format cells as
0\:00\:00

"onlyinsyder" wrote:

I am having a little problem here. I am employed for a small television
station, and have created a worksheet to track how many times each commercial
spot is played, and at what time it is played. I need to be able to insert
the times quickly without typing in the colon, and have Excel insert the
colon for me (i.e. 12657 = 1:26:57). I figured that I could format my cell as
follows: "h:mm:ss"
This did not do the trick. When I insert a time without the colon, it
fromats it with a strange date. Anyone know of a custom format that will
work for this problem?

That does make the cell look like a time, but it doesn't make the value a real
time.



mama no teeth wrote:

Try this

Format cells as
0\:00\:00

"onlyinsyder" wrote:

I am having a little problem here. I am employed for a small television
station, and have created a worksheet to track how many times each commercial
spot is played, and at what time it is played. I need to be able to insert
the times quickly without typing in the colon, and have Excel insert the
colon for me (i.e. 12657 = 1:26:57). I figured that I could format my cell as
follows: "h:mm:ss"
This did not do the trick. When I insert a time without the colon, it
fromats it with a strange date. Anyone know of a custom format that will
work for this problem?

--

Dave Peterson

actually, all I need it to do is look like a time, not actually become a
real time. this works perfectly. thanks.
"Dave Peterson" wrote:

That does make the cell look like a time, but it doesn't make the value a real
time.



mama no teeth wrote:

Try this

Format cells as
0\:00\:00

"onlyinsyder" wrote:

I am having a little problem here. I am employed for a small television
station, and have created a worksheet to track how many times each commercial
spot is played, and at what time it is played. I need to be able to insert
the times quickly without typing in the colon, and have Excel insert the
colon for me (i.e. 12657 = 1:26:57). I figured that I could format my cell as
follows: "h:mm:ss"
This did not do the trick. When I insert a time without the colon, it
fromats it with a strange date. Anyone know of a custom format that will
work for this problem?

--

Dave Peterson