**Please Note: **
This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. 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.

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). 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 T2D As Long Application.Volatile 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 * 30 + 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 RemainingTime As Long Application.Volatile NumHours = Raw \ (CLng(30 * 60) * 60) RemainingTime = Raw Mod (CLng(30 * 60) * 60) NumMinutes = RemainingTime \ (60 * 30) RemainingTime = RemainingTime Mod (60 * 30) NumSeconds = RemainingTime \ 30 RemainingTime = RemainingTime Mod 30 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.

*ExcelTips* is your source for cost-effective Microsoft Excel training.
This tip (8353) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: **Calculating TV Time**.

**Save Time and Supercharge Excel!** Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out *Excel 2010 VBA and Macros* today!

Collect a series of times in a worksheet, and you might need to adjust those times for various time zones. This involves a ...

Discover MoreDo you need to enter negative times into a worksheet? Excel doesn't really provide a way to do that, but understanding why ...

Discover MoreWhen adding values to a time to calculate a new time, you may naturally choose to use the TIME function. This can cause some ...

Discover More**FREE SERVICE:** Get tips like this every week in *ExcelTips,* a free productivity newsletter. Enter your address and click "Subscribe."

2014-01-07 14:04:27

Bryan

From what little I know about the film industry, 30fps is not a hard-and-fast rule. These macros could benefit from an optional parameter that sets the fps (default to 30). Then instead of multiplying/dividing by 30, you'd use the fps variable.

Lastly, unless my math is way off, I'd challenge the assertion that you can't convert frames to time. 1 frame is 3.86e-7 days, meaning that if you want the integer value of 24:0:0:1, that resolves to 1.00000038580247, which is enough precision to get the frame back out. In fact, at 30fps, you only need 3 digits to accurately back calculate the frames, so Excel will be able to handle a very large number before you run into problems (I spent waaaay too much time creating some macros to test, and I hit stack overflow errors at 239999999:59:59:29 before I hit a rounding error -- that's 1 frame less than 10 *million* days!!).

That being said, there's no technical reason not to use the multiplication version. That should work up to 999,999,999,999,999 frames, or 385,802,469.1 days.

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 © 2017 Sharon Parq Associates, Inc.

## Comments