Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Calculating TV Time.
Written by Allen Wyatt (last updated September 12, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
John works in the TV industry, where timing is done to a resolution finer than a second. Television video must take into account hours, minutes, seconds, and frames. (There are thirty frames per second.) John was wondering if there was a way to handle frames in Excel.
There is no way to handle frames as part of the native time values in Excel. (In the television industry a time value that includes frames is often referred to as "timecode" or "time code.") There are, however, a couple of things you can do to work with frames. Perhaps the most obvious suggestion is to keep hours, minutes and seconds as a regular time value, and then put frames in a separate cell. The immediate drawback to this approach is that calculations for the "TV times" are not as easy as they would be if they were represented in a single value.
A way around this is to try to do your own calculations in a macro. Excel goes through an internal process of converting times to decimal values that can be worked with very easily. You could simulate this same conversion process, converting a time value (including frames) to a decimal value. The TV time, in the format 00:29:10:10, could be stored in a cell (where Excel will treat it as a string) and then converted to a value by the macro.
There is a problem here, of course: You cannot convert the time to a true decimal value between 0 and 1 like Excel does for times. The reason has to do with the limits on Excel's significant digits. To arrive at a value, you would divide the hours by 24, the minutes by 1440 (24 * 60), the seconds by 86400 (24 * 60 * 60) and the frames by 2592000 (24 * 60 * 60 * 30, assuming you are working at 30 frames per second). When you start getting into values that small, it exceeds Excel's limits of maintaining everything to fifteen significant digits. Thus, you end up with unavoidable rounding errors on the frames value.
One solution to this problem is to not try to work with decimal values between 0 and 1, but instead work with integers. If you convert the string time into an integer value that represents the number of total frames in the time, then you can easily do math on the resulting value. The following macro will do the conversion of a string in the format already mentioned:
Function Time2Num(Raw) As Long Dim FirstColon As Integer Dim SecondColon As Integer Dim ThirdColon As Integer Dim NumHours As Integer Dim NumMinutes As Integer Dim NumSeconds As Integer Dim NumFrames As Integer Dim FrameRate As Integer Dim T2D As Long ' Change the following to the number of frames ' per second with which you are working FrameRate = 30 FirstColon = InStr(Raw, ":") SecondColon = InStr(FirstColon + 1, Raw, ":") ThirdColon = InStr(SecondColon + 1, Raw, ":") NumHours = Val(Mid(Raw, 1, FirstColon - 1)) NumMinutes = Val(Mid(Raw, FirstColon + 1, SecondColon - 1)) NumSeconds = Val(Mid(Raw, SecondColon + 1, ThirdColon - 1)) NumFrames = Val(Mid(Raw, ThirdColon + 1, Len(Raw))) T2D = CLng(NumHours) T2D = T2D * 60 + NumMinutes T2D = T2D * 60 + NumSeconds T2D = T2D * FrameRate + NumFrames Time2Num = T2D End Function
To see how this works, if you have a string such as 37:15:42:06 in cell A4, and you use the formula =Time2Num(A4), the result is the value 4024266, which is the number of frames in 37 hours, 15 minutes, 42 second, and 6 frames. To convert such values back to an understandable time, you can use the following function:
Function Num2Time(Raw) As String Dim NumHours As Integer Dim NumMinutes As Integer Dim NumSeconds As Integer Dim NumFrames As Integer Dim FrameRate As Integer Dim RemainingTime As Long ' Change the following to the number of frames ' per second with which you are working FrameRate = 30 NumHours = Raw \ (CLng(FrameRate * 60) * 60) RemainingTime = Raw Mod (CLng(FrameRate * 60) * 60) NumMinutes = RemainingTime \ (60 * FrameRate) RemainingTime = RemainingTime Mod (60 * FrameRate) NumSeconds = RemainingTime \ FrameRate RemainingTime = RemainingTime Mod FrameRate NumFrames = RemainingTime Num2Time = Format(NumHours, "00") & ":" & _ Format(NumMinutes, "00") & ":" & _ Format(NumSeconds, "00") & ":" & _ Format(NumFrames, "00") End Function
By combining the two functions, you can do some math with the times. For instance, suppose you had the time 00:29:10:10 in cell A4 and the time 00:16:12:23 in cell A5. If you put the following formula in a cell, you can find out the difference between the two times:
=Num2Time(Time2Num(A4)-Time2Num(A5))
The result is 00:12:57:17.
The examples presented here are rudimentary; they don't take into account any error handling or limit checking on the times used. You can either expand on the examples to fit your needs, or you can look to a third-party source. For instance, you can find an explanation (with a sample workbook) for NTSC and PAL times at the following URL:
http://www.kenstone.net/fcp_homepage/timecode_spreadsheet.html
There are also other macro-based solutions floating around the Internet. The best approach is to use your favorite search engine and look for "timecode excel" or "time code excel" (without the quotes). You'll find plenty of examples of code you can start with.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8353) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Calculating TV Time.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
Do you need to enter negative times into a worksheet? Excel doesn't really provide a way to do that but understanding why ...
Discover MoreWhen you enter a time value into Excel, the program tries its hardest to make the value into a valid time. This can lead ...
Discover MoreYou know what time it is, right? (Quick; look at your watch!) What if you want to know what time it is in Greenwich, ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2023-09-13 10:07:42
Allen, an interesting tips on calculating TV time. As an FYI only in case you have other comments come back to you, the frame rate in North American TV is not exactly 30, in fact, for most major broadcasters (NBC, CBS, PBS, HBO, etc....consciously EXCLUDING ABC), images are transmitted and displayed in frames, but rather in fields. I'm not going to bore you with a lesson on broadcast engineering... but there is an important subtlely here that affects your Excel tip today. Images from most of the major broadcasters are interlaced (which is a hold over from when our North American television system was created decades ago. In current HD TV in North America, a TV screen is made up of 1080 lines top to bottom. They are "painted" on our screen in a unique order. First the odd lines 1,3,5 etc. and then the even lines 2,4,6 etc. Each scan top to bottom is called a "field" so it take 2 fields to make a full frame. In the B&W days, this "field" rate was locked to the power line frequency of 60 Hz. So although the full frame rate was 30 frames per second, it is actually 60 fields per second. Now when color got introduced and all of the math was worked out, (before HD TV), some things changed....ever so slightly, including the field rate and it's corresponding frame rate...some of which persists even today. Bringing this full circle, the North American field rate is actually 59.94xxxxx fields per second, resulting in a frame rate of 29.97xxx frame per second. This has been a monumental curse on the broadcast industry for decades.....and the conversion between real time and TV time...actually requires calculations. The SMPTE (Society of Motion Picture and Television Engineers - a Standards Body for the media industry) invented SMPTE Time Code, which is a method of labelling each "field" and "frame" in a recording. In Europe, where the TV frame is exactly locked to the power line at 50hz, the math is easy. However, here in North America, it's not so easy, because of the slight change in frame rate, simply counting fields/frames will NOT keep you precisely aligned with "real time". As I said, this has been a monumental aggravation for TV production since the invention of color TV. So...the SMPTE enhanced the SMPTE Timecode standard, to add a drop frame mode....which brings back alignment of "TV time" with real time." and actually involves dropping some frame labels. It's all math, which I won't bore you with here. Unfortunately, the jargon used to describe TV related matters these days, has become really, really sloppy because saying 30 frames/second is easier than saying 29.97xxx/second in North America! And further, with extensive of use computer generated and manipulated imagery (which is 100% of what you see now), none of these terms remain "absolute." As a profession, our company develops, designs and builds television facilities. This frame rate thing....can be a real "behind the scenes" quagmire requiring careful attention. If you read any kind of video specs, you'll see the quiet notation of 1080i, 1080p and 720p. . This "i" means interlaced interlaced lines 1,3,5 then 2,4,6 and "p" means progressively scanned frames top to bottom 1,2,3,4,5,6, most of which is at 59.94hz, "i" or "p", so the math in your formula will need some extra precision. There are volumes of more data behind this discussion, which I'll save you from having to read! I should also note that I thoroughly enjoy your Word and Excel tips....and your courses and e-books, as I spend some of my time writing Proposals (to those broadcasters to build new facilities) and your insights (to a broadcast engineer) on Word and Excel have been very valuable! Thank you!
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments