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

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

## View Solution Only

Top Expert 2015

Commented:
Dilitiv,

So for value which is

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

Saurabh...
Commented:
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")
``````
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

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

Thanks,
Dina

Commented:
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")
``````

Option-2

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