Custom Formatting, for 8 digit time (timecode)

[phillr]

I'm using Excel 2000. For our company, we do alot of managing of video tape
materials. The timing within the videotape itself uses an 8 digit numerical
system. To help us keep track of the tapes, we have a big Excel spreadsheet
with each tape having 8 digit numerical data on it (trying to make it
sensible for people not familiar with video ).

Essentially we are currently manually typing in these eight digit numbers
and, for example, we type this = 01:27:35:12

What i'd like to type is 01273512 and Excel will automatically add the
colons ":" in for me. If anybody could create a format cell custom formula
for me that'd be great! (e.g. i right click a cell -> format cells -> custom
-> and input your wonderful formula).

Hopefully this can be done on Excel 2000

 

[phillr]

think i got it!

##":"##":"##":"##

seems to be working fine. don't know much about custom formatting, so don't
know if there's any faux pas or 'inefficient' coding or what not, so feel
free to comment on it

 

[Bob Phillips]

00\:00\:00\:00

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[SteveW]

Yes it will look right,

but if the data is time
dont you want this to be
treated as HH:MM:SS.ss

A2= 12345678 format how you like
B2 =
=TIME(INT(A2/1000000),INT(MOD(A2,1000000)/10000),INT(MOD(A2,10000)/100))

And some extra bit to give you thousandths of a second on the end,
trying to get the units right

Steve

 

[SteveW]

update that with the correct bit.
A2= 12345678 format how you like
B2
=TIME(INT(A2/1000000),INT(MOD(A2,1000000)/10000),INT(MOD(A2,10000)/100))+(MOD(B4,100)/100/60/60/24)

The last bit converts the 78 into 1/100s
Format B2 as hh:mm:ss.00

Steve