We help IT Professionals succeed at work.

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

dilitv
dilitv asked
on
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
Comment
Watch Question

Top Expert 2015

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

Author

Commented:
I am expecting the answer that looks like 00:06:35:05 (hours:minutes:seconds:frames"

Thanks,
Dina
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
Top Expert 2015

Commented:
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
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!