Converting milliseconds into frames

[howdiepowdie]

Hello,

Just came across your profile.
I was wondering if you can help me to create an excel formula to convert milliseconds e.g. present in SRT files into frame timecode.

So this:

00:15:24:250
into this:
00:15:24:10
considering also the frame rate.

It does not have to be super precise. 😉

I get sometimes dubbing dialogue scripts not as proper timecode.
That way I have to do some workarounds to convert it into SMPTE TC. My DAW does accept only timecode in format: hh:mm:ss:ff.

I mentioned before it does not have to be super precise, because most of the time I have to adjust the take length manually anyway while previewing the different characters. But I already have them setup and I do not have to generate them manually. That would be a very time consuming task.

So maybe you could give me a hand with my issue?
Thank you and have a great one.

Richard

[cgranier]

Happy to help. Do you have a sample file I could take a look at? Or a list of values you receive with the corresponding values you would expect from the formula?

I could probably adapt these to work with your issue.

[howdiepowdie]

Awesome !

Ok here you go.
I will attach some excel data from a file I got and what it should be in the end.
The Milliseconds tab is what I got, the frames is what it should be in the end. The framrate was 23,98.

Thank you so much.

Have a great one.

Best
Richard
milliseonds_frames_TEST.xlsx

[cgranier]

Ok. Here you go. Sorry for the delay, but I was traveling.

I will add all the values into the formula, to make it easier for you to include into your workflow, but here are some details:

Since your frame rate is 23.98, you can divide all milliseconds by 1000/23.98 to get the frames. At this frame rate, there are about 41.7 milliseconds per frame.

This formula will also round up to the nearest frame. I noticed in your sample file that sometimes you were rounding up and sometimes you weren't, even though the decimal part was under .5. I will provide two formulas, one that always rounds up and one that rounds to the nearest whole number.

Assuming your Milliseconds are in B1, use this formula to get SMPTE time code:

=LEFT(B1,8)&":"&TEXT(CEILING(VALUE(RIGHT(B1,3))/(1000/23.98),1),"00")

To round to the nearest whole number, use this version:

=LEFT(B1,8)&":"&TEXT(ROUND(VALUE(RIGHT(B1,3))/(1000/23.98),0),"00")

What this formula does:

LEFT(B1,8) grabs the first eight characters starting from the left from the timecode in milliseconds.

&":" adds a colon

&TEXT(ROUND(VALUE(RIGHT(B1,3))/(1000/23.98),0),"00") adds the milliseconds converted to frames:

VALUE(RIGHT(B1,3))/(1000/23.98) grabs the last three characters from the timecode in milliseconds, converts them into number format, and divides this number by 1000/23.98 (the milliseconds per frame).

ROUND(VALUE(RIGHT(B1,3))/(1000/23.98),0) takes the number above and rounds it up or down to the nearest whole number. The version using CEILING, rounds up to the nearest whole number.

TEXT(ROUND(VALUE(RIGHT(B1,3))/(1000/23.98),0),"00") converts the result to text using a leading zero if necessary, so that 2 becomes 02.

Let me know if this works ok for you.

[howdiepowdie]

Oh that is awesome. I will check it out and let you know. Thank you so much.

Have a great day :-D

[cgranier]

Did the code work?

[howdiepowdie]

Hey Carlos,

thank you so much and sorry for the delay. I was pretty sick. But I am better now and the heat is killing us here in Germany ;-)

Unfortunately the formula does not work in my excel at the moment. Then I realized maybe it has to do because my Excel is in German. So I started to look for the German command expressions. Also I realized that in German the ; semicolon is the separation character and not the comma. I will try to translate it and get back to you as soon as possible. however I may understand the formula and maybe I can get it work if all commands are translated in German.

Thank you so much for your work.

I keep you posted :-D

All the best
Richard

[cgranier]

Hey, I hope you're feeling better.

I'll take a look at the German Excel equivalents. Might just need to exchange commas for periods (or semicolons).

Once we figure out the equivalent formats, it should work, as the logic works over here.

[howdiepowdie]

Ok. So no translation needed. however I had to replace the comma character , with the semicolon and the period from 23.98 with a comma but it does not work . It gives me an error and says #NAME?

[howdiepowdie]

Yeah your response was quicker than mine . LOL

[howdiepowdie]

But I do understand the logic. So we are a step closer ;-)

[howdiepowdie]

=LEFT(B2;8)&":"&TEXT(CEILING(VALUE(RIGHT(B2;3))/(1000/23,98);1);"00")

[cgranier]

Ok, so I installed the Function Translator (Add-Ins) and I get this when I translate the formula into German:

=LINKS(B2;8)&":"&TEXT(OBERGRENZE(WERT(RECHTS(B2;3))/(1.000/23,98);1);"00")

which seems to match with your last message.

[howdiepowdie]

Ok. it seems my excel absolutely wanted everything in GERMAN.

NOW IT WORKS. AWESOME, CARLOS. Great work . Thank you so much.

[cgranier]

Try to isolate the different parts of the formula, and try them on their own. This should tell us where the NAME? error is happening.

[cgranier]

Ok. it seems my excel absolutely wanted everything in GERMAN.

NOW IT WORKS. AWESOME, CARLOS. Great work . Thank you so much.

That is awesome! Ignore my last test then ;-)

Stay cool!