About Drop-Frame Timecode
In order to do math with timecode, which is in an hours:minutes:seconds:frames format, it must be converted into a number representing the total number of frames. You then do your math with frame numbers, and convert back to the timecode format. For non-drop-frame timecode, this is trivial (I’ve included that code below as well for completeness). It gets complicated when you’re dealing with drop-frame timecode. If you’re new to drop-frame, it’s a way of accurately measuring the running time of video that doesn’t run at an even frame rate. In the United States and several other countries, video runs at 29.97 frames per second, but we usually count it at 30 fps. This means that, after a while, the timecode does not accurately reflect the running time of the video. For an hour-long program, the timecode gets to be about 3.6 seconds off. The solution to this problem is drop-frame timecode, which is sort of like leap year for timecode. At 29.97 fps, every minute (except minutes divisible by ten), you skip counting the first two frames. You know you’re looking at drop frame timecode because the colon between minutes and frames is usually replaced by a semicolon. For example, you go:
- and then you skip to…
- I’m using the symbol % to represent the modulo operator. Modulo refers to the remainder that’s left after division. 30 / 7 is 4 with a remainder of 2, so 30 % 7 would be 2. Most languages use the same % notation for modulo. As with integer division, modulo behaviors vary among programming languages when negative numbers are involved. Again, we’re not going to worry about those distinctions here.
Finally, the Code!
So, without further ado, here’s some pseudocode in a vaguely Java style. First, we’ll convert a frame number into hours, minutes, seconds, and frames.
[This code has been updated on 8/8/2010 to correct a bug on line 27 caught by Jean-Baptiste Mardelle of Kdenlive. It was updated again on 1/4/2012 to fix a minor syntax error and correct a display issue with < and > symbols. Thanks to Martin Baker for catching those. I updated it in October, 2019 to remove 24p and to address an issue related to negative values]
Frame Number to Drop-Frame Timecode
Drop-Frame Timecode to Frame Number
For the sake of completeness, here’s the code for the relatively trivial task of converting between frame numbers and non-drop-frame timecode. This code is general and will work for any frame rate including 23.976p.
Frame Number to NDF
NDF to Frame Number
A Note About 24p
In NTSC countries, 24p footage actually runs at 23.976 fps. 23.976 drop-frame timecode is not part of the SMPTE timecode spec and most editing programs don’t have a 24p drop-frame option. There are good reasons for this. Let’s think about how drop-frame timecode works for 29.97. Television programs commonly run in 30 minute increments, so drop-frame timecode should work in such a way that it is guaranteed to be a correct representation of the running time at least every 30 minutes. In fact, drop-frame is cyclical every ten minutes and, therefore, is guaranteed to accurately represent run-time every ten minutes.
At 30 frames per second, to convert ten minutes of programming to a frame count, you multiply:
30 (frames per second) * 60 (seconds per minute) * 10 (minutes) = 18,000
However, since NTSC video operates at 29.97, not 30 frames per second, the actual number of frames in ten minutes of NTSC video is:
29.97 (frames per second) * 60 (seconds per minute) * 10 (minutes) = 17,982
That means that every ten minutes, drop-frame timecode needs to make up for the discrepancy between 18,000 frames and 17,982 frames, which is 18 frames. And that’s exactly what drop-frame timecode does. By skipping two frames every minute, except for minutes divisible by ten, the timecode skips exactly 18 frames every ten minutes and therefore, even if drop-frame timecode may deviate from actual running time by a frame or two within a ten minute cycle, at the end of that cycle, it is guaranteed to be consistent.
Now let’s run those same numbers for 23.976:
24 (frames per second) * 60 (seconds per minute) * 10 (minutes) = 14,400
23.976 (frames per second) * 60 (seconds per minute) * 10 (minutes) = 14,385.6
Already, we can see that we might be in trouble here. The discrepancy over ten minutes is 14.4 frames, which has a decimal component. Even at 30 minutes or 60 minutes, the discrepancy between 24 and 23.976 has a decimal. That means that no matter what rules you set for dropping frames, there’s no way to make it come out evenly over a 60 minute period. There’s actually no way to make it come out evenly even over a 24 hour period. There’s simply no way to devise a formula to accurately do drop-frame for 23.976 footage.
The issue here is that 23.976 has more significant digits than 29.97.* Over a ten minute period, you’re multiplying your frame rate by 600 (60 seconds per minute * 10 minutes). That’s enough to multiply the 0.97 part of 29.97 into an integer, but at 23.976, you’re still stuck with that pesky 0.6.
So what should you do if you have 23.976 footage? Well, below is some code to get accurate timings, at least. We’ll call it pseudo-drop-frame for 24p. This is not part of the SMPTE spec, but it will give you a good estimate of the actual running time of your program.
Basically, what we’re doing here is calculating the true runtime in seconds and then using the remaining fractions of a second to calculate frames.
Practically, this means that a 30 minute 24p program should be (about) 43,157 frames long and a 60 minute program should be (about) 86,314. That translates to 24p non drop-frame timecode of 00:29:58:05 and 00:59:56:10 respectively.
Frame Number to Pseudo-Drop-Frame 24p
Pseudo-Drop-Frame 24p to Frame Number
Master sync / timecode generators like our Evertz 5601MSC use a GPS reference, so not only is the video reference spot on for frequency, the time code is extremely stable. Nevertheless, at any given moment there will be some amount of error between the (drop frame) time code versus real time. The solution to this is called “jam sync,” which essentially resets the time code, forcing it to match the real time once again. Some systems perform the jam when the error exceeds a set threshold; as you can imagine, this creates a fairly large jump and can be disruptive. Our equipment automatically performs the jam daily (ours takes place at 2am, when the automation doesn’t care). Not only does this correct for the relatively small mathematical imprecision you notice, it also makes provision for events like leap seconds that occur from time to time.
Naturally, this is only significant for operations that use time code to reflect real time — to control automation, or to index continuous recordings like air check systems. For time code within the context of specific program content, even shows that run several hours, uncorrected drop-frame code is accurate enough to run cleanly.
I’ve had to jam sync devices on a film set before, this is a way to be sure that all your equipment (cameras, audio, slate, etc.) have matching timecode. Since different internal timecode generators can drift slightly over time, you periodically re-sync everything to one master timecode generator. I didn’t know that similar equipment is used by broadcast engineers, but it makes sense. Another user, dcwar, pointed out that the manual for the Evertz Master Clock System that Jeff uses is available online. Page 54 has more detail about the jam sync process as well as a couple very cool graphs showing how drop-frame timecode can drift over the course of a day.
So there you have it, that’s why the very slight error in rounding frame rates to 29.97 doesn’t cause all television programming to slip by a few frames each day.
I just want a spreadsheet formula for summing numbers of the form of: hh:mm:ss;ff for drop frame. I assume that can be done with integer() & MOD() .
xx:xx:xx is stored internally by decimal part of a day.
xx:xx:xx.xxx will be evaluated correctly for my spreadsheet app.
(BTW mod(x,1) will return the decimal fractional part of x)
I’m going to solve it, but you could simplify the discussion and be helpful by discussing very clearly and exactly when and how the standard (if there is a conformant standard) does at every step in the process.
Realize this is a different task than showing a program that does it. There are EXACT concepts involved (hopefully).
BTW my website is many years out of date. I have been retired for 15 years.
I had the code commented fairly extensively, but it looks like a WordPress update hid all the comments. They should be back now and hopefully that clarifies things. Let me know if you have any specific questions on what’s going on.
To answer your question, I think what you’re trying to do is add two dropframe timecode numbers together (in a spreadsheet), correct? So
02:04:46;26 + 05:41:32;18 = HH:MM:SS;FF? For dropframe timecode, best practice is to convert both of the timecodes to frame numbers, do the math with the frame numbers, and then convert the result back to timecode at the end. I’m not aware of an algorithm that accurately can do dropframe timecode math without having to do that conversion.
So in the example above, we convert
224,382using the dropframe timecode to frame number algorithm (assuming 29.97 fps).
614,164using the same method. We sum those together to get
838,546and then use the frame number to DF algorithm to convert back to
Having attempted to do dropframe timecode math in Excel in the past, I can say, it’s a real pain. Your best bet is to write a VBA function (or Apps Script in Google Sheets) to do the timecode math using the code here as a guideline.
However, there may be an alternative that comes with a large caveat, which I’ll discuss in a moment. As you noted, Excel stores time as the decimal part of a day. We could do something like that with the timecode. We could convert
02:04:46;26to seconds and get
(2*60*60 + 4*60 + 46 + 26/29.97) = 7,486.867534200867. Similarly,
20,492.6006006006. If we wanted, we could divide that by the number of seconds in a day to get a number that Excel would recognize as a time, but we’ll skip that for now. We can add those two numbers together to get
27,979.468134801467. We can then reverse the process to get back timecode. Note that we’re doing integer division (\) for some of this:
Seconds = 27,979.468134801467
Hours = Seconds \ (60*60) = 7
Seconds = Seconds - Hours*60*60 = 2,779.4681348014674;
Minutes = Seconds \ 60 = 46
Seconds = Seconds - Minutes*60 = 19.468134801467386
Seconds then equals the integer part of that last number (19) and Frames will equal the fractional part (0.468134801467386) times the framerate, 29.97, which equals 14.03 and we’ll round that to the nearest integer to get 14. So our end result is
And here we have a problem. Using this method, we get
07:46:19;14and using the conversion method I discussed above, we get
07:46:19;16, a discrepancy of two frames. What’s going on?
Well, it turns out we’re measuring two different things here. Dropframe timecode aims to be accurate to real time at every ten minute mark. But over the course of ten minutes, that accuracy can drift and dropframe timecode is actually not accurate to real time. Right before we do the dropframe corrections, dropframe can be off by a few frames, that’s what we’re correcting for. Page 55 of the Evertz manual that I linked to in the post contains a really nice graph showing how dropframe timecode can stray from realtime over the course of an hour. We see on the 10 minute marks, it’s very accurate, but just before that, it’s leading realtime by two frames, and that’s exactly what we see in our sample. The timecode we’re working with,
07:46:19;xxis very close to a ten-minute boundary, and our dropframe calculation is leading our realtime calculation by two frames. In the television world, this is okay. Programming is built around half hour segments, so as long as we’re accurate at those half hours, if things get a little muddled in the middle, it doesn’t matter.
So, all of that is a long way of saying, how complicated this is in Excel really depends on what you want to do. If you need frame accurate timecode math, you’re in for a world of hurt and you’re probably better off writing a VBA script rather than trying to come up with a formula. Using the second method, which I think is manageable as a formula, you will get time-accurate numbers that will not always agree with the “correct” timecode math numbers, but the difference should only be a max of two frames and maybe that’s okay for what you need to do. You can also use this second method to very easily convert to the Excel time format, which may be an added benefit.
Hope that’s useful for you!