dsol21
Level 1 (18 points)
iWork

Q: Using film timecodes as a data type?

I work in film, and I was hoping to use Numbers to help me create costings for a VFX project (on a cost per second basis). I've tried creating a custom datatype, and it almost works apart from one significant hurdle. Which is - that while there's HOURS : MINUTES : SECONDS  that there isn't any equivalent of FRAMES (which are usually 24, 25 or 30 depending on which video format you're working in).

 

I can use Adobe Premiere to export a batch list of files as a CSV, which come into Numbers fine. However, manipulating this data is - as far as I can tell - impossible without video timecode support. Has anyone worked out a solution for this? Or is there a feature suggestion link for me to forward this on to the Numbers team?

 

Many thanks

Mac Pro, OS X Mountain Lion (10.8.3)

Posted on Aug 8, 2016 6:52 AM

by SGIII,Solvedanswer
SGIII SGIII
Level 6 (10,622 points)
Mac OS X
A:

Here's another approach that takes advantage of the ability of Numbers to automatically recognize times and calculate duration when you subtract them.

 

Screen Shot 2016-08-08 at 1.26.13 PM.png

 

The timecode format is assumed to be HH:MM:SS:FF .

 

The formula in D2, filled down is:

 

   =LEFT(B,8)−LEFT(A,8)+DURATION(0,0,0,0,(RIGHT(B,2)−RIGHT(A,2))÷30)

 

The LEFT(B,8)−LEFT(A,8) simply subtracts one HH:MM:SS from the other. Numbers automatically recognizes these as times that, when subtracted, result in a duration.

 

The DURATION(0,0,0,0,(RIGHT(B,2)−RIGHT(A,2))÷30)  simply subtracts one FF from the other, divides by the FPS (the 30 in this example) and uses the DURATION function to convert to a duration so the result can simply be added to the duration calculated in the first part of the formula.

 

SG

Posted on Aug 8, 2016 10:42 AM

Close
dsol21

Q: Using film timecodes as a data type?

  • All replies
  • Helpful answers

  • by SGIII,

    SGIII SGIII Aug 8, 2016 7:04 AM in response to dsol21
    Level 6 (10,622 points)
    Mac OS X
    Aug 8, 2016 7:04 AM in response to dsol21

    You can give feedback/suggestions at Numbers > Provide Numbers Feedback in your menu.

     

    But first, perhaps you could post more specifics of your problem, what your imported data look like, and what result you want.

     

    If the imported data includes a column for frames I would think you could convert that.  In any case, specifics would help.

     

    Wayne describes how to post a screenshot in this user tip.

     

    SG

  • by dsol21,

    dsol21 dsol21 Aug 8, 2016 7:28 AM in response to SGIII
    Level 1 (18 points)
    iWork
    Aug 8, 2016 7:28 AM in response to SGIII

    Here's a screenshot of an imported CSV from Premiere (with a bit of tidying up to remove empty rows and columns).
    As you can see, timecode is written in HH:MM:SS:FF  (Hours, minutes, seconds, frames) format. Not as separate columns  -  if you did, it would be much harder to calculate useful values like clip duration from IN and OUT timecodes.

    Screen Shot 2016-08-08 at 15.23.47.png

  • by dsol21,

    dsol21 dsol21 Aug 8, 2016 7:46 AM in response to dsol21
    Level 1 (18 points)
    iWork
    Aug 8, 2016 7:46 AM in response to dsol21

    It looks like there's a plugin to do it in Excel, but I'd really rather use Numbers if I can! https://latenitefilms.com/blog/calculating-timecode-in-excel/

  • by Wayne Contello,Helpful

    Wayne Contello Wayne Contello Aug 8, 2016 8:20 AM in response to dsol21
    Level 6 (18,977 points)
    iWork
    Aug 8, 2016 8:20 AM in response to dsol21

    select the cells you show in the screenshot (above) and copy, then paste the table here so we can use the same data without having to type it in.

     

    I suggest adding a column that calculates the duration.

     

     

    something like this:

    Screen Shot 2016-08-08 at 10.03.30 AM.png

     

    D2=(3600×MID(B2, 1, 2)+60×MID(B2, 4, 2)+MID(B2, 7, 2)+MID(B2, 10, 2)÷30)−(3600×MID(A2, 1, 2)+60×MID(A2, 4, 2)+MID(A2, 7, 2)+MID(A2, 10, 2)÷30)

     

    this is shorthand for... select cell D2, then type (or copy and paste from here) the formula:

    =(3600×MID(B2, 1, 2)+60×MID(B2, 4, 2)+MID(B2, 7, 2)+MID(B2, 10, 2)÷30)−(3600×MID(A2, 1, 2)+60×MID(A2, 4, 2)+MID(A2, 7, 2)+MID(A2, 10, 2)÷30)

     

    the divide by 30 (bolded below) assumes 30 frames per second:

    =(3600×MID(B2, 1, 2)+60×MID(B2, 4, 2)+MID(B2, 7, 2)+MID(B2, 10, 2)÷30)−(3600×MID(A2, 1, 2)+60×MID(A2, 4, 2)+MID(A2, 7, 2)+MID(A2, 10, 2)÷30)

     

     

    you can fill this down by selecting cell D2, copy

    select cells D2 thru the end of column D, paste

  • by dsol21,

    dsol21 dsol21 Aug 8, 2016 8:46 AM in response to Wayne Contello
    Level 1 (18 points)
    iWork
    Aug 8, 2016 8:46 AM in response to Wayne Contello

    Here's a paste of the table from Numbers (if this works!)

    IN

    OUT

    CLIP

    00:48:54:22

    00:49:03:21

    AW_shot01.mov

    00:49:20:12

    00:49:30:03

    AW_shot02.mov

    00:50:54:14

    00:50:59:08

    AW_shot03_v02.mov

    00:51:19:14

    00:51:32:01

    AW_shot04.mov

    01:00:00:00

    01:00:05:02

    AW Shots - .Sub.09.redo position fixed.mov

    01:04:15:22

    01:04:23:16

    AW Shots - .Sub.01.mov

    01:14:58:09

    01:15:09:21

    AW Shots - .Sub.02.mov

    01:21:09:08

    01:21:14:02

    AW Shots - .Sub.03.mov

    01:21:38:14

    01:21:46:06

    AW Shots - .Sub.04.mov

    01:29:47:21

    01:30:05:12

    AW Shots - .Sub.05.redo - move removed.mov

    01:30:22:21

    01:30:28:06

    AW Shots - .Sub.06 with 2  Handle at start due to fade.mov

    01:30:22:21

    01:30:28:06

    AW Shots - .Sub.06.mov

    01:51:52:06

    01:52:00:18

    AW Shots - .Sub.07.mov

    01:52:07:07

    01:52:16:00

    AW Shots - .Sub.08.mov

    01:54:16:16

    01:54:22:11

    AW Shots - .Sub.10.mov

    01:54:39:04

    01:54:45:06

    AW Shots - .Sub.11.mov

    01:55:32:06

    01:55:35:23

    AW Shots - .Sub.12.mov

    01:55:49:10

    01:55:52:12

    AW Shots - .Sub.13.mov

    02:17:57:02

    02:18:08:03

    AW Shots - .Sub.14.mov

    02:21:46:13

    02:22:09:04

    AW Shots - .Sub.15.mov

    02:22:32:14

    02:22:36:14

    AW Shots - .Sub.17.mov

    02:23:24:05

    02:23:45:04

    AW Shots - .Sub.22.mov

    02:24:02:01

    02:24:14:22

    AW Shots - .Sub.18.mov

    02:24:38:22

    02:24:43:20

    AW Shots - .Sub.19.redo shot one - the move is as it was filmed.mov

    02:24:43:20

    02:24:49:19

    AW Shots - .Sub.19.redo shot two.mov

    02:25:21:07

    02:25:26:14

    AW Shots - .Sub.21.mov

  • by Wayne Contello,

    Wayne Contello Wayne Contello Aug 8, 2016 9:07 AM in response to dsol21
    Level 6 (18,977 points)
    iWork
    Aug 8, 2016 9:07 AM in response to dsol21

    Here is another variation:

    Screen Shot 2016-08-08 at 11.06.03 AM.png

     

    D2=DURATION(0, 0, 0, 0, (3600×MID(B2, 1, 2)+60×MID(B2, 4, 2)+MID(B2, 7, 2)+MID(B2, 10, 2)÷30)−(3600×MID(A2, 1, 2)+60×MID(A2, 4, 2)+MID(A2, 7, 2)+MID(A2, 10, 2)÷30))

     

    select cell D2, copy

    select cells D2 thru the end of column D, paste

  • by SGIII,Solvedanswer

    SGIII SGIII Aug 8, 2016 10:42 AM in response to dsol21
    Level 6 (10,622 points)
    Mac OS X
    Aug 8, 2016 10:42 AM in response to dsol21

    Here's another approach that takes advantage of the ability of Numbers to automatically recognize times and calculate duration when you subtract them.

     

    Screen Shot 2016-08-08 at 1.26.13 PM.png

     

    The timecode format is assumed to be HH:MM:SS:FF .

     

    The formula in D2, filled down is:

     

       =LEFT(B,8)−LEFT(A,8)+DURATION(0,0,0,0,(RIGHT(B,2)−RIGHT(A,2))÷30)

     

    The LEFT(B,8)−LEFT(A,8) simply subtracts one HH:MM:SS from the other. Numbers automatically recognizes these as times that, when subtracted, result in a duration.

     

    The DURATION(0,0,0,0,(RIGHT(B,2)−RIGHT(A,2))÷30)  simply subtracts one FF from the other, divides by the FPS (the 30 in this example) and uses the DURATION function to convert to a duration so the result can simply be added to the duration calculated in the first part of the formula.

     

    SG

  • by dsol21,

    dsol21 dsol21 Aug 8, 2016 10:43 AM in response to SGIII
    Level 1 (18 points)
    iWork
    Aug 8, 2016 10:43 AM in response to SGIII

    That's a really clever solution. I've sent a feature request for timecode support to the Numbers team, but in the meantime I think this will work really well. Thanks a million!

  • by SGIII,

    SGIII SGIII Aug 8, 2016 11:51 AM in response to dsol21
    Level 6 (10,622 points)
    Mac OS X
    Aug 8, 2016 11:51 AM in response to dsol21

    Glad it worked for you.  I was scratching my head on this one, and learned a bit about timecodes and how good Numbers is at detecting date-time strings and saving the user from having to do lots of date-time calculations.  Thanks for the interesting question, and the green tick!

     

    SG