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))
–Update: A tip from Ken Case led to the solution. The finished formula, which produces properly formatted smpte 24p non-drop timecodes, is the following:
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"))
