User profile for user: sam2miller
sam2miller Author
User level: Level 1
4 points

Complicated Numbers Formula Problem

Hello!


I am working on a CCSL document for a film, and the majority of my cells are filled with TIMECODE, formatted: 00:00:00:00


I just found out I'm working off a slightly incorrect version of the film, which means that while all of the timings are correct to each other, they are off with the final film by about 3 seconds.


I need to write a formula, that I can apply to entire rows, that SUBTRACTS numbers in 2 columns (00:00:03:04) to be precise.


So for instance, I need to turn:

01:23:20:13

into

01:23:17:09


Is there any way to do this??? I'm happy to get tricky with it, I just don't know Numbers quite well enough to crack this nut on my own.


Thank you so much!!!!!



Posted on Feb 21, 2024 10:47 AM

Reply
11 replies
User profile for user: sam2miller
sam2miller Author
User level: Level 1
4 points

Feb 22, 2024 12:27 PM in response to SGIII

WOW. This is incredible, thank you so much. Truly cannot believe how much time this will save me, you are a godsend.


I've been experimenting with a bunch of different points in the film, and for the most part the formula has been working great. The only issue is that occasionally when I input a timecode, the frame rate defaults to 92.


I've been trying to understand the formulas, but I don't know enough about either Time Code or modular arithmetic to diagnose the problem. I've included a link to Numbers doc below - the first one is an example of it working perfect, the second is an example of it flipping to 92.



Thank you so much!!!!


[Edited by Moderator] 


Reply
User profile for user: SGIII
SGIII
User level: Level 8
37,604 points

Feb 22, 2024 6:39 PM in response to sam2miller

This links have been removed. Could you post a screenshot showing the problem?


shift-command-4, select area, release, start new post here and use the mountains-and-moon 'Image insertion' icon beneath the compose window to attach the image file from the Desktop.


FPS should be just a manual input in the cell in the little table at the upper left. Are you saying the formulas aren't applying that value?


SG

Reply
User profile for user: 6x6
6x6
User level: Level 7
20,223 points

Feb 23, 2024 10:57 AM in response to sam2miller

Hi Sam,


The formula in the Timecode spreadsheet given above is slightly incorrect: the frame number needs to be rounded. Here is the corrected formula with your 2nd example:



EDIT: I don’t know if, in the movie business, frames are rounded up or down. The formula above uses the classic mathematical rounding. Instead of the ROUND function, you could use the ROUNDUP or ROUNDDOWN functions to suit your needs.

Reply
User profile for user: SGIII
SGIII
User level: Level 8
37,604 points

Feb 21, 2024 6:21 PM in response to sam2miller

See the "Subtracting ..." sheet in this Timecode Numbers document (Dropbox download).


I did this a few years ago. As you say, it's complicated, with lots of pesky details under the hood.


The table at the top shows the step-by-step calculation. As you can see the first row arrives at the same result as in the example in your post.


The "compact" form at the bottom of the sheet is the same table, just with calculation columns hidden. Once you are satisfied you are getting the expected results, then you can just use the compact table.


SG

Reply

This thread has been closed by the system or the community team. You may vote for any posts you find helpful, or search the Community for additional answers.

Complicated Numbers Formula Problem

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.
Learn more Sign up