Number Formating Cells (Time)

[Guest]

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?

 

[Bob Phillips]

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)

 

[Dave Peterson]

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

 

[Guest]

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

Brent

 

[Guest]

Try this

Format cells as
0\:00\:00

 

[Dave Peterson]

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

 

[Guest]

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