Timecode format change

[jmanlasman]

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

 

[Erny]

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

 

[Erny]

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...

 

[jmanlasman]

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...

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

 

[Erny]

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

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...

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

 

[jmanlasman]

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




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

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

 

[jmanlasman]

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




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

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

 

[jmanlasman]

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




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

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

 

[jmanlasman]

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




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

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

 

[jmanlasman]

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




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

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

 

[cschiller1]

There's really no need to post this FOUR times.

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

 

[Erny]

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

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

Newsbeitrag


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

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