 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / June 2007Timecode format change
How would I change the format of a standard time like 1:31 (1 minute 31 seconds) to a DV timecode format of 00:01:31:00 easily? Thanks Hello, If the format of the answer can be text, you could use the following formula (eample if your original time is specified in cell A1): =SUBSTITUTE(TEXT(A1,"hh:mm:ss.00");".";":") you will however not be directly able anymore to use this as a value, but as text. If you need to use the value somewhere, you would have to refer to the original cell, but if your wish is only to display in the format explained, it should work. Kind regards, Erny > How would I change the format of a standard time like 1:31 (1 minute > 31 seconds) to a DV timecode format of 00:01:31:00 easily? > > Thanks Sorry, I slipped this one (I use different formatting), the correct formula should be: =SUBSTITUTE(TEXT(A1,"hh:mm:ss.00"),".",":") Voilà, that's it...:-) > Hello, > [quoted text clipped - 15 lines] >> >> Thanks On May 30, 12:08 pm, "Erny" <Erny-removehyphensandthistext- Me...@hotmail.com> wrote: > Sorry, I slipped this one (I use different formatting), the correct formula > should be: [quoted text clipped - 24 lines] > > >> Thanks Thank you very much for your help! You dont know how much time you are saving me. One quick last question though. Lets say I have the numbers entered in minutes from A1 to A10 and I wanted to use this formula to quickly do them all, how would I change the thing you gave me? I tried A1:A10 and the substitute formula, but that still only did A1. Thank you again for your solution! -Jeremy Hi Jeremy, The easiest would be probably to insert a column next to the one holding your data (let's say column B) and use the formula in there. Replicating it downwards should work. Kind regards, Erny On May 30, 12:08 pm, "Erny" <Erny-removehyphensandthistext- Me...@hotmail.com> wrote: > Sorry, I slipped this one (I use different formatting), the correct > formula [quoted text clipped - 29 lines] > > >> Thanks Thank you very much for your help! You dont know how much time you are saving me. One quick last question though. Lets say I have the numbers entered in minutes from A1 to A10 and I wanted to use this formula to quickly do them all, how would I change the thing you gave me? I tried A1:A10 and the substitute formula, but that still only did A1. Thank you again for your solution! -Jeremy On May 30, 3:26 pm, "Erny" <Erny-removehyphensandthistext- Me...@hotmail.com> wrote: > Hi Jeremy, > [quoted text clipped - 52 lines] > > -Jeremy I've ran into another problem. Since this is a DV tape, the time goes from 0:00 - 1:00:00. I have for example 28:48 (28 minutes 48 seconds) and it is being converted by excel to 00:04:48:00 because 24 hours in a day. How would I fix this? Thanks for your help, Jeremy On May 30, 3:26 pm, "Erny" <Erny-removehyphensandthistext- Me...@hotmail.com> wrote: > Hi Jeremy, > [quoted text clipped - 52 lines] > > -Jeremy I've ran into another problem. Since this is a DV tape, the time goes from 0:00 - 1:00:00. I have for example 28:48 (28 minutes 48 seconds) and it is being converted by excel to 00:04:48:00 because 24 hours in a day. How would I fix this? Thanks for your help, Jeremy On May 30, 3:26 pm, "Erny" <Erny-removehyphensandthistext- Me...@hotmail.com> wrote: > Hi Jeremy, > [quoted text clipped - 52 lines] > > -Jeremy I've ran into another problem... since this is coming from a DV tape, its going from 00:00 - 1:00:00. So heres an example, a time I wanted to convert 28:41 (which would be 28 minutes 41 seconds) is being understood by excel as 00:04:48:00. Because its taking 24 as one day. How would I fix this? Thanks for your help, Jeremy On May 30, 3:26 pm, "Erny" <Erny-removehyphensandthistext- Me...@hotmail.com> wrote: > Hi Jeremy, > [quoted text clipped - 52 lines] > > -Jeremy I've ran into another problem... since this is coming from a DV tape, its going from 00:00 - 1:00:00. So heres an example, a time I wanted to convert 28:41 (which would be 28 minutes 41 seconds) is being understood by excel as 00:04:48:00. Because its taking 24 as one day. How would I fix this? Thanks for your help, Jeremy On May 30, 3:26 pm, "Erny" <Erny-removehyphensandthistext- Me...@hotmail.com> wrote: > Hi Jeremy, > [quoted text clipped - 52 lines] > > -Jeremy I've ran into another problem. Since this is a DV tape, the time goes from 0:00 - 1:00:00. I have for example 28:48 (28 minutes 48 seconds) and it is being converted by excel to 00:04:48:00 because 24 hours in a day. How would I fix this? Thanks for your help, Jeremy There's really no need to post this FOUR times. > On May 30, 3:26 pm, "Erny" <Erny-removehyphensandthistext- > Me...@hotmail.com> wrote: [quoted text clipped - 65 lines] > Thanks for your help, > Jeremy Hi Jeremy, I understand that in the original format there are also not the portions of seconds expressed (otherwise the length factor needs to be reviewed in the following): I would replace then in the formula A1 by IF(LENGTH(A1)<6,"00:"&A1,A1) resulting in a formula as follows: =SUBSTITUTE(TEXT(IF(LENGTH(A1)<6,"00:"&A1,A1),"hh:mm:ss.00"),".",":") Hope this helps! Erny On May 30, 3:26 pm, "Erny" <Erny-removehyphensandthistext- Me...@hotmail.com> wrote: > Hi Jeremy, > [quoted text clipped - 56 lines] > > -Jeremy I've ran into another problem. Since this is a DV tape, the time goes from 0:00 - 1:00:00. I have for example 28:48 (28 minutes 48 seconds) and it is being converted by excel to 00:04:48:00 because 24 hours in a day. How would I fix this? Thanks for your help, Jeremy
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.