Timecode Calculator
July 5, 2005 10:27 AM Subscribe
Timecode filter: how do I subtract a given number of frames from 400 timecodes in an Excel spreadsheet?
Video timecodes are in the following format (for NTSC non-drop frame) - hh:mm:ss:ff. The ff represents frames, of which there are 30 per second (00 - 29). I want to subtract 5 frames from 400 different timecodes, all of which are less than a minute long.
The best I've come up with so far is removing the hh:mm using find and replace (because all the video clips are under a minute), then using the LEFT and RIGHT Excel functions to separate the seconds and frames into individual columns, then multiplying the seconds by 30 to give their length in frames, adding the seconds column to the frames column to give total frames and then subtracting 5.
This is as far as I've got - I'm not sure if this is the best method, and I don't know how to return the total frames to the original hh:mm:ss:ff format (including that single digit seconds and frames require a leading 0 added).
I've Googled all over the place and whilst there are several timecode calculators available, they only do individual calculations as opposed to batches. And yes, I've tried www.belle-nuit.com - unfortunately the Excel macro doesn't work correctly on current versions of Excel, and the Filemaker Pro plug in appears to only do individual calculations.
I have Excel 03 on XP Pro, and easy access to Macs if required. Any ideas? Thanks.
Video timecodes are in the following format (for NTSC non-drop frame) - hh:mm:ss:ff. The ff represents frames, of which there are 30 per second (00 - 29). I want to subtract 5 frames from 400 different timecodes, all of which are less than a minute long.
The best I've come up with so far is removing the hh:mm using find and replace (because all the video clips are under a minute), then using the LEFT and RIGHT Excel functions to separate the seconds and frames into individual columns, then multiplying the seconds by 30 to give their length in frames, adding the seconds column to the frames column to give total frames and then subtracting 5.
This is as far as I've got - I'm not sure if this is the best method, and I don't know how to return the total frames to the original hh:mm:ss:ff format (including that single digit seconds and frames require a leading 0 added).
I've Googled all over the place and whilst there are several timecode calculators available, they only do individual calculations as opposed to batches. And yes, I've tried www.belle-nuit.com - unfortunately the Excel macro doesn't work correctly on current versions of Excel, and the Filemaker Pro plug in appears to only do individual calculations.
I have Excel 03 on XP Pro, and easy access to Macs if required. Any ideas? Thanks.
Response by poster: alright - you guys ROCK!!! I'd been pulling my hair out on that one all week - thanks!
posted by forallmankind at 5:22 PM on July 5, 2005
posted by forallmankind at 5:22 PM on July 5, 2005
« Older Unplugging firewire webcam causes kernel panic | Have any MeFites participated in the Hospitality... Newer »
This thread is closed to new comments.
Set the formula for G1 to
=CONCATENATE("00:00:",TEXT(trunc(f1/30,0),"00"),":",TEXT(mod(f1,30),"00")).Fill it down to G400.
posted by fleacircus at 11:18 AM on July 5, 2005