convert frames to 24p ND timecode in Excel
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 EDL1 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"))
- acronym for Edit Decision List [↩]
















April 11th, 2008 at 10:25 am
You can format each segment with leading zeros by using the TEXT() function:
CONCATENATE(ROUNDDOWN(B2/86400,0),”:”,
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″))
Hope this helps!
April 11th, 2008 at 4:14 pm
Thanks Ken!
The complete formula is:
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″) )