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

# Calculating TV Time by Allen Wyatt
(last updated June 15, 2019)

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:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

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 Office 365. You can find a version of this tip for the older menu interface of Excel here: Calculating TV Time.

##### Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

##### MORE FROM ALLEN

Importing a Text File and Inserting after a Bookmark

Word macros are a great way to automate some of the ways in which you create documents. If you have a need to insert the ...

Discover More

Tracing Dependent Cells

Cells that use the information in a particular cell are called dependent cells. Excel provides auditing tools that allow ...

Discover More

Borders on Multiple Paragraphs with Differing Indents

When you place a border around a paragraph, the position of that border is determined, partially, by the indent with ...

Discover More Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

##### More ExcelTips (ribbon)

Taking the Time into Account in a Formula

Need to check the current time in a formula you are putting together? It can sometimes be tricky to remember what Excel ...

Discover More

Automatically Converting to GMT

You know what time it is, right? (Quick; look at your watch!) What if you want to know what time it is in Greenwich, ...

Discover More

Converting Numeric Values to Times

If you have a bunch of times entered into cells without the colon between the hours and minutes, chances are good that ...

Discover More
##### Subscribe

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

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 3 - 2?

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)

##### This Site

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.