In this example, column G is determining a timecode value, 10:45:46:2 based on the frame number in B, 0929906. That timecode value should end in :02, and will once I figure a good way to add leading zeros in an Excel formula.

The other day I was presented with an Excel spreadsheet listing a bunch of clips by frame number, and I needed to find the scene and shot numbers for each clip from an EDL¹ which listed the clips by timecode.

Rather than spend a lot of time with a timecode calculator converting each frame number to 24fps non-drop SMPTE timecode, I came up with a formula that would automatically generate and display the timecode for a given frame number. I didn’t spend the time to figure out how to coerce excel into adding leading zeros where necessary, so it doesn’t always generate timecodes that are formatted properly, but it was good enough for my purposes. I’m posting the formula here in case any other filmmakers find it useful, and if you happen to know how to modify this formula so that it does add leading zeros, please consider posting your code as a comment here. We’re all in this together. On that note, on to the formula.

For a given frame number in column 2, row B, the following formula will convert that frame number to timecode (24p, ND):

CONCATENATE( ROUNDDOWN(B2/86400,0),":", ROUNDDOWN(MOD(B2,86400)/1440,0), ":", ROUNDDOWN(MOD(MOD(B2,86400),1440)/24,0), ":", MOD(MOD(B2,1440),24))

CONCATENATE( TEXT(ROUNDDOWN(B2/86400,0),"00"),":", TEXT(ROUNDDOWN(MOD(B2,86400)/1440,0),"00"), ":", TEXT(ROUNDDOWN(MOD(MOD(B2,86400),1440)/24,0),"00"), ":", TEXT(MOD(MOD(B2,1440),24),"00"))

1. acronym for Edit Decision List [↩]

This entry was posted on Friday, April 11th, 2008 at 12:16 am and is filed under Development, Filmmaking, Geeky, Microsoft. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.