Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom Formatting, for 8 digit time (timecode)
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 :D). 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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom Formatting, for 8 digit time (timecode)
think i got it! :D
##":"##":"##":"## 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 :D |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom Formatting, for 8 digit time (timecode)
00\:00\:00\:00
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "phillr" wrote in message ... 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 :D). 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom Formatting, for 8 digit time (timecode)
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 On Wed, 31 Jan 2007 15:01:01 -0000, phillr wrote: think i got it! :D ##":"##":"##":"## 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 :D |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom Formatting, for 8 digit time (timecode)
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 On Wed, 31 Jan 2007 15:32:35 -0000, SteveW wrote: 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 On Wed, 31 Jan 2007 15:01:01 -0000, phillr wrote: think i got it! :D ##":"##":"##":"## 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 :D |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formatting time cell | Excel Discussion (Misc queries) | |||
Refresh PivotTable, lose custom cell formatting | Excel Discussion (Misc queries) | |||
conditional formatting with time values | Excel Discussion (Misc queries) | |||
01/01/1900 and Time Formatting | Excel Discussion (Misc queries) | |||
time formatting and time categorizing (vlookup or data validation) | Excel Worksheet Functions |