Level 1

5 points

Timecode Formula by SGIII, Questions...

Hello. SGIII created an excellent Numbers document to add timecode in HH:MM:SS:FF (it can be found here... https://discussions.apple.com/thread/252142434?answerId=254071786022#254071786022). This have been a huge help to me, and everything was working very well at 25fps. Unfortunately I can't get it working correctly at 24fps. At first glance at 24fps appears to operate correctly, if you add 3 frames it works correctly, but if you add 1 or 2 frames the formula returns an incorrect result, and I've spent a day trying to figure this one out, but I can't do it. I assume that it is to do with the translation of milliseconds to fames (and frames to milliseconds), and that dividing something by 25 gives cleaner numbers than dividing be 24.

As an example of 24FPS:

01:03:15:00 + 01:03:15:01 should equal 02:06:30:01, but the formula result is 02:06:30:08.

01:03:15:00 + 01:03:15:02 should equal 02:06:30:02, but the formula result is 02:06:30:96.

01:03:15:00 + 01:03:15:03 should equal 02:06:30:03, and the formula result is correct at 02:06:30:03.

Adding 3, 6, 9, 12, 15, 18, or 21 frames works, all other numbers in-between, don't.

All formula calculations at 25FPS work as expected.

If SGIII was available to help that would be wonderful, but I'm looking for any help really.

I'm using Numbers 10.1 on Mojave, and 11.1 on Monterey.

A part of the formula is not working under Numbers 11.2 and shows an error "Argument 2 in LEFT must be greater than or equal to 0." but that's an entire other issue, hence why I'm not using Numbers 11.2.

Thanks in advance.

Posted on Feb 2, 2022 1:37 AM

Top-ranking reply

SGIII

Level 8

38,006 points

Posted on Feb 2, 2022 4:42 AM

Yes, I see new versions of Numbers have broken the original document (probably a good thing).

Here's a revised version (Dropbox download) that I think is giving the results you specify.

On the first sheet is a working table with the guts in hidden columns. It looks like this.

On the second sheet is the same table with columns unhidden and a full explanation.

View in context

Similar questions

Insert date and time as a static (non updating) entry in a single cell with a simple keyboard shortcut I just read a pile of threads on this topic going back to 2014, as well as in non-Apple forums. It seems there is yet to be a simple method to input date & time in a single cell that is static (will not auto update when the file is opened later). The workarounds I have found can get the job done but are either confusing to set up or they require so many keystrokes that it's inefficient. I need to input the date and time often hundreds of times daily so a quick keystroke would be gold for me. Here are the workarounds I have found with my questions: 1) Input the following in a cell: =Now Then to get the value from it without auto update, copy (cmd C) and then paste the value without the formatting (cmd shift V). And if it is frequently used a smart tip is to leave the "=Now" in a dedicated cell so it is always available to be copied and then pasted (value only). Q: By default it will auto update; it would be such an easy solution if there was a formula for current date and time that would NOT auto update. Is there some derivative of this formula that will not auto update the date and time? 2) Automator SGII (forum mod) suggested an Automator. I ran into problems (error message. “System Events got an error: Automator Workflow Runner (WorkflowServiceRunner, Today) is not allowed to send keystrokes.” A solution was offered to such errors but I could not figure it out on my system. Q: I was not able to follow the instructions in other posts on this as they were spread across several back-and-forth discussions, if you know how this works could you type out the steps from start to finish, including various permissions that need to be granted? 3) The Apple method Barry (forum mod) posted this option: 1) double click in the cell, 2) insert menu > date & time (or press cmd semi-colon), then once done, go outside the cell, then back to it again and double click and a drop-down menu will appear, in that menu choose from the selection list of a variety of date & time options. This will then give the current date & time in a static form. Q: If a user does only the first step the time defaults to midnight; the user needs to then go outside the cell & go back in to double click again as per Barry's post to choose a date and time format which will then put the current time. This works but it takes so many steps! I don't know if it will remain static or if it will update like the =Now method. Is there a way to automate these steps or to assign a single keystroke that will do both steps? It is good that this method works, but I can not see myself using it in its current form considering I may have to do it over a hundred times each day; it would need to be somehow more efficient in order to be worth it. Also can anyone confirm if it will remain static so that it will NOT auto update? (bonus question: Barry had said that cmd semi-colon was a shortcut; when I do that instead of the date & time being inserted my cursor jumps to a different page in the Numbers spreadsheet. I looked in my keyboard settings under the 'shortcuts' tab and I could not see that cmd semi-colon was assigned to anything. Any insight?) 4) Third party tools such as "Typinator" This is one of perhaps many utilities out there that can automate typing functions; in a thread someone had said they used it to input current date and time but on the website for the utility I could not find any info on it being able to input static date and time for Apple Numbers, but perhaps it or another third party tool can work. Q: Anyone aware of a tool that will input static date and time with a keystroke (into a single cell)? FYI I did input a suggestion in the Apple feedback site; but considering there are forums on this with hundreds of pages of comments going back to at least 2014 and maybe earlier and it still is not available, I'm not sure if my feedback will be seen or make a difference. The ideal solution that I would love would be to have something as simple as what Google sheets has, there is a kestroke for (static) date and a variation of that keystroke for (static) date and time; it is super easy. *please Apple* OMG this is such a widely used and very needed feature. Any feedback on this is appreciated. Rod
2067 4

3 replies

Top-ranking reply

SGIII

Level 8

38,006 points

Feb 2, 2022 4:42 AM in response to DVD404

Yes, I see new versions of Numbers have broken the original document (probably a good thing).

Here's a revised version (Dropbox download) that I think is giving the results you specify.

On the first sheet is a working table with the guts in hidden columns. It looks like this.

On the second sheet is the same table with columns unhidden and a full explanation.

DVD404 Author

Level 1

5 points

Feb 2, 2022 5:16 AM in response to DVD404

Wow, what a wonderful piece of art you have created there. Thank you so, so much, not just for taking the time to work it all out, but also for the explanations. You have made my day. All the very best to you. Thank you.

SGIII

Level 8

38,006 points

Feb 2, 2022 5:19 AM in response to DVD404

Glad it worked on your end. Thanks for feedback, and the green tick!

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.

Timecode Formula by SGIII, Questions...