Converting seconds into SMPTE Time Code (HH:MM:SS:FF) where frames per second value is 23.97

Hello,

I am working with Excel file (see example attached) where I need to get different time points in a SMPTE Time Code. In the columns G, I, and L we have time in seconds. I need to come up with the formula that will convert seconds into the time code. This time code is supposed to have a format HH:MM:SS:FF where FF stands for frames. So all decimal seconds should be converted into frames number, taking in account that we have 23.97 frames per second.
Also I have to count the duration of video clip by subtracting the value in column H from the value in column J. The duration should also have the format HH:MM:SS:FF.

Thanks in advance for the help,
DinaSample.xlsx
dilitvAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
Dilitiv,

So for value which is

395.188184 what answer your expecting or looking forward for?

Similarly for value-->627.145773.. what answer you anticipatE?

Saurabh...
0
Ejgil HedegaardCommented:
The In point in G17 is 3706.892018.
3706 seconds are 1 hour, 1 minute and 46 seconds.
The fraction .892018 with 23.97 frames per second gives 21.3816714600002, equal to frame number 21.
So the result should be 01:01:46:21
Is that correct?

The formula making the string is
=TEXT(INT(G17/3600),"00:")&TEXT(INT(G17/60-INT(G17/3600)*60),"00:")&TEXT(INT(G17)-INT(G17/60-INT(G17/3600)*60)*60-INT(G17/3600)*3600,"00:")&TEXT(INT(MOD(G17,1)*23.97),"00")

Open in new window

The out point formula are the same, with I17 instead of G17.
For the duration G17 are replaced with the time difference (I17-G17) in the formula.
See sheet.
Sample-SMPTE.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dilitvAuthor Commented:
I am expecting the answer that looks like 00:06:35:05 (hours:minutes:seconds:frames"

Thanks,
Dina
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Ejgil HedegaardCommented:
That means that the frame fraction must be rounded up, so .188184 * 23.97 = 4.51 means it is in frame number 5.
Check sheet.
Sample-SMPTE.xlsx
0
Saurabh Singh TeotiaCommented:
Dina,

You can use couple of formulas which is..

Option-1

=TEXT(INT(G4)/(24*3600),"hh:mm:ss:")&TEXT(CEILING(MOD(G4,1)*23.97,1),"00")

Open in new window


Option-2

=TEXT(TIME(,,INT(G4)),"hh:mm:ss:")&TEXT(CEILING(MOD(G4,1)*23.97,1),"00")

Open in new window


Your workbook attached...

Saurabh...
Sample-SMPTE.xlsx
0
Adrienne LCommented:
so, if I wanted to convert seconds to HH:MM:SS:FFF I would just add a 0 to the end of the equation :
=TEXT(TIME(,,INT(G4)),"hh:mm:ss:")&TEXT(CEILING(MOD(G4,1)*23.97,1),"000")

Correct?

Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.