Timecode format from HBO

Hi everyone,
we've received an Excel spreadsheet for subtitling an HBO trailer. Only problem is that TC looks like this:  IN: 00:00:04:745  OUT: 00:00:09:204

Any idea how we can transform those into a 25fps based TC ? (possibly automatically...)

Thanks

Patrice

It seems that the last numbers are milliseconds. You could add some columns to the spreadsheet. Then, use a string operation to "grab" the last digits, e.g., 745 for the IN timecode. Divide 745 by 1000 and multiply by 25 and round that result to the nearest whole number to get the frame number.

So, for 00:00:04:745, you would end up with 745/1000*25 = 18.625, which rounded gives 19. So, in terms of a 25 FPS timebase, you would then have 00:00:04:19.

So, converting ms into frames should be pretty straightforward in the spreadsheet itself...

I created a example spreadsheet in Numbers that you can use as a model. I exported the spreadsheet to Excel format, but I'm not sure if it actually works in Excel. Both files are in the attached archive.

Nice one, Dave!

Hi, and sorry for the late thank you. I will try that.

However, has anyone here seen such a way of describing TC ever? In 40+ years of post this is a first for me... also, does this hint that some tool somewhere would be capable of importing such a spreadsheet directly to turn it into subs?

thank you again.

I've seen ms used in timecode in a few places, most often, I believe, with subtitles. Using ms is frame rate agnostic, so it's not a bad choice since it can be used in any situation. In fact, many subtitle file formats use ms instead of frames.

FYI, my example's "conversion" logic probably doesn't account for edge cases where the frame gets rounded to 25. This case should increment the seconds by one and the frame number should be zero. So, for something like 00:00:10:998, converting to a 25 fps timebase should yield 00:00:11:00 (my example would yield 00:00:10:25). Fixing this would require using seconds only, instead of hours, minutes, and seconds (along with the fractional seconds).

Most subtitle file formats are pretty simple, so it wouldn't be very difficult to export a spreadsheet as a "comma-separated value" (CSV) text file and process each line accordingly.

---

Okay, sorry... Let's start over...

One way to get your spreadsheet content into FCP (if you have the subtitle text along with the IN and OUT time values) is to:

1. Arrange the columns of the spreadsheet so that IN, OUT, and SUB are next to each other in that order.

2. I don't have EXCEL so I can only go from memory. Numbers is finicky about the next steps (you can only have the three columns with no header columns). The problem is that Numbers won't let you (as far as I can tell) export only a selection of rows and columns, but EXCEL does.

3. Good news is you don't need to worry about converting the timecode.

4. So, we have three columns: IN, OUT, SUB. With those selected for all of the subtitle rows, you simply export the data as a CSV file. Using your sample timecode, the first line of the exported CSV file will look like (in something like TextEdit):


The rest of the file should look similar to that.

5. One tool I found, which I use myself, is "SRT Converter" (it's around $10 in the Mac App Store). It will convert the CSV file into an SRT subtitle file. Other tools may do the SRT or other conversions. The first subtitle of the resulting SRT file should look like (in TextEdit):



---

Applications like Subvert ( www.subvertapp.com/ ) can bring SRT files into FCP.

Sorry for any confusion. I was initially focused on converting the timecode into 25 fps. Hope this helps...

Even better! Once I have the SRT file I import it into either Annotation Edit from Zeitanker (one of the best subtitling app and with great support IMHO or into FCPX SRT IMPORTER from Ulti.media ( ulti.media/fcp-srt-importer-2/ ).

The explanation about the reason for the HH:MM:SS:thousands time format makes a lot of sense, thank you.

Pushing this thing a little further, I hit a bump on the road to happiness: SRT Converter will only create a 30 fps based srt file from my csv...

Any idea why? (I cannot see any indication of time base in the CSV file).

The SRT file should contain the same time info as in the spreadsheet: hh:mm:ss:ms, though the format is specifically hh:mm:ss,ms (comma as the last delimiter, e.g., 00:00:04,724). In other words, the timestamps for the subtitles in an SRT file should be framerate agnostic. On import, FCP should handle any conversion.[1]

SRT Converter had the framerate dulled out when I ran some tests earlier (it may have shown as 30). I think that that feature is applicable for instances where the originating timecode was in hh:mm:ss:frames...

[1] en.wikipedia.org/wiki/SubRip

Ok I tried again:

• It looks indeed that FCPX will adapt the frame rate on import, but this only works if you want captions (or ITT captions). (Unless there is a way in FCPX to convert captions to titles?)

• I had not adjusted frame rate in FCPX SRT Importer hence the 30fps result. (DOH)

• Subvert is a nice way to go too, with more control, thanks for the tip.

Hi,
I exported captions to ITT
cut the other info away in text app
put it in spreadsheet and with search & replace I reduced timecode to seconds

00:09:00 Why are you so passionated about hospitality

To convert captions to titles you can use my X-Title Extractor.
ITT or SRT (or many other ) directly to titles with X-Title Importer.

Andreas! Long time no see (we've been in touch before).

Glad to know that, I'll add them to my toolbox!

Thanks