[Solved] User-defined time format (Timecode)

[SIP4Stock]

Hi Guys

I am hoping that this is a stupid question, but am unable to find any info on the forum or Web.

I need to format a cell in Calc to the following international time code standard for film and video, hh:mm:ss:00 with the 00 being milliseconds. The system will not allow me to deviate from this hh:mm:ss.00 with a . rather than a : between ss and 00. This makes it impossible for me to calculate the length of a video clip, which is what I need to do.

I am running OpenOffice 4.1.1 on Windows 10. My two input columns are start and finish time code (17:19:41:00 and 17:20:11:12) and I need Calc to automatically determine the clip length 00:00:30:12

How do I make Calc accept my user-defined time format?

Many Thanks
Russ

[Villeroy]

Decimals (of seconds) are displayed as decimals with a decimal point or decimal comma depending on the number format locale. There is no way around this unless you concatenate some text values which can not be calculated with.

Apart from this (cosmetic) problem, I think that the :12 in 17:20:11:12 counts the frames in a second (usually 1/25th of a second).

[acknak]

Greetings and welcome to the community forum!

Sorry, no, there's no support for custom time formats like this.

The best you can do, as far as I know, is storing the inputs as text and converting to numeric values to do the arithmetic.

... hh:mm:ss:00 with the 00 being milliseconds. ...

Sorry, but are you sure about that? I thought the last number was a frame count.

... My two input columns are start and finish time code (17:19:41:00 and 17:20:11:12) ...

I assume these are text, right?

If so, you can convert to numeric values (days) with something like
=TIMEVALUE(LEFT(A2;8))+(VALUE(RIGHT(A2;2)/FDiv))

Where FDiv is either 2592000 for 30 fps, or 86400000 for milliseconds.

With a little more effort, you could convert to seconds instead of days.

Once you do the arithmetic (End-Start), you can do the conversion back to text; again, there's no built-in format to do that.

I remember we went through this before. Try a search--I think there's a complete working sample there.

Edit: Oops, typing too slow again. I see you already have the same answer!

Edit: PS:

Here's the older thread I was thinking of:
viewtopic.php?f=9&t=62200

[SIP4Stock]

My Bad, not milliseconds at all. Frames it is!! on a frame rate of 30fps.
Sorry for the confusion.

I did read the following post http://www.oooforum.org/forum/viewtopic.phtml?t=142764 but this dose not work when the input start and end time codes are over 60 seconds, it seems. I get ERR:508.

I will take a look at v ... =9&t=62200 now.....

Thanks

OK, tried that, URL not found

Any other ideas?

[SIP4Stock]

Sorry Guys, me again. Thanks you Villeroy and acknak for your input, but I am still lost.

How do I determine what format the timecode cells are in? I imported all the metadata from a .ALE file export from DaVinci Resolve. When I go to Format Cells, they show up as Number- General.

I am an entry level user, so please be patient and spell it out, loud and SLOW :-O

Many Thanks
Russ

[Villeroy]

Spreadsheets know numbers and text. Formulas may return error as a third type.
12:34:56:23 is not a numeric value. It is a text, a sequence of digits and colons. Number formats do not apply to text values.
[Tutorial] Ten concepts that every Calc user should know
viewtopic.php?f=9&t=62200 describes pretty well how to convert this kind of text into a number representing a time span.
Err508 indicates that your formula (which you don't share with us) is syntactically incorrect.

[SIP4Stock]

Hi Villeroy

Many thanks. I followed the steps on the link as you suggested. I get Timecode start and stop now of 1039.683333 and 1040.183333. When I subtract the one from the other to determine the length of the clip, I get clip length of 0.5, half a minute I expect. This must be in the format of 00:00:30:00. How do I convert it back?

Regards
Russ

[Villeroy]

This is not a question of formatting. Formatting does not change any values.
We need to convert a number to text which changes not only the value but the type of value.

[MrProgrammer]

Hi, and welcome to the forum.

This makes it impossible for me to calculate the length of a video clip, which is what I need to do.

Read section 4. Times in cells in Ten concepts that every Calc user should know. Study the attachment carefully.

201508071727.ods

(8.65 KiB) Downloaded 216 times

My two input columns are start and finish time code (17:19:41:00 and 17:20:11:12) and I need Calc to automatically determine the clip length 00:00:30:12.

I get Timecode start and stop now of 1039.683333 and 1040.183333. When I subtract the one from the other to determine the length of the clip, I get clip length of 0.5, half a minute I expect. This must be in the format of 00:00:30:00.

Your calculations are wrong. At 30 FPS the clip length is 0.5066666… minutes. The attachment calculates the correct answer, 00:00:30:12.

am unable to find any info on the forum or Web

A search of this forum would have found several topics, and the link to [Solved] Convert hours:minutes:seconds:frames to minutes (topic 62200).
Search found 9 matches: +timecode

I am running … on Windows 10.

My condolences to you.

Edit: I discovered a condition that attachment 201508070919.ods did not handle correctly. It has been replaced with 201508071727.ods

If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[SIP4Stock]

Hi Guys

Many Thanks. I have read most of the timecode topics/threads that you have both suggested, most of them before posting this question, but was unable to get any of them to resolve my issue. Sorry to have waisted your time. I thought that with so many film timecode users in the marketplace, that there would be a simple formatting selection somewhere in the software. But no luck.

I will just have to calculate and enter each one manually, all 2310 of them.

Thanks again
Russ

[jrkrideau]

/

I will just have to calculate and enter each one manually, all 2310 of them.

This seems a bit drastic. What does the .ALE file look like and where does one get a sample file? If we get a chance to see what the raw data is we might be able to come up with a solution. Without some idea of the form of the original data the people here are guessing at what you need.

You can upload a sample file here . There is an attach file tab at the bottom of the Reply window or perhaps you can supply a link to a sample file?

[SIP4Stock]

Hi jrkrideau

Thanks. I am open to any suggestion. I have no problem importing the Data, only with calculating the Clip Duration.

I think the file uploaded.

Regards
Russ


I can't see the file here, will try again.........

No luck. I get the message that "The extension ALE is not allowed"

[acknak]

You can upload any file here by first putting it into a .zip file archive.

Even better (and easier) would be if you attach your spreadsheet with the imported data that you're trying to work with.

If the file is too big (128k limit) then you have to use a file sharing site and post the link here.

Sorry, there really is no turnkey solution for this. Either you understand enough about the gritty details of using a spreadsheet or you get someone else to do it for you.

[Villeroy]

Calculating 10.000 time codes: http://www.mediafire.com/view/nbn4pocm8 ... ecodes.ods

Edit: New hyperlink to new file. Fixed rounding problem with floats

A spreadsheet is not a tool for any particular purpose. It is a simplified programming language for non-programmers. No programming language handles 12:34:56:13 out of the box.

[jrkrideau]

Nonsense Villeroy,
A spreadsheet is a perfect tool for spreading errors, omissions, despondency and confusion.

Spreadsheet Horror Stories

Or as Pat Burns puts it:
It is dead easy to get an answer from a spreadsheet, however, it is not necessarily easy to get the right answer.
Spreadsheet Addiction

[MrProgrammer]

I will just have to calculate and enter each one manually, all 2310 of them.

This seems like a lot of work to me. Perhaps you could use this attachment. It does timecode calculations using multi-precision arithmetic techniques.

201508071738.ods

(17.3 KiB) Downloaded 288 times

It is dead easy to get an answer from a spreadsheet, however, it is not necessarily easy to get the right answer.

True. I discovered that the SECOND() function did not work the way I expected it to, so I replaced the attachment in the post I made 8 hours ago. It uses the MOD() and INT() functions instead of HOUR(), MINUTE(), and SECOND().

[SIP4Stock]

Gosh, thanks to all of you for your responses.

I have read them all and will use MrProgrammer's attachment. It work very well indeed, Thanks MrProgrammer

Thanks again
Russ
(Overwhelmed)

[Villeroy]

Yes, MrProgrammer has a working solution with no rounding errors. I tested his formulas with my 10,000 pairs of values and found 4 places where my formulas are off by one second.

This is a database solution with the same 10,000 pairs of time codes: http://www.mediafire.com/download/2ny0f ... ecodes.odb
It yields the exact same results as MrProgrammer's sheet formulas. The incoming data could be dynamically linked to a text file.

[Lupp]

I came a bit late to the thread but found it interesting.
Thanks to MrProgrammer. His solution, using enough helper columns, is well structured and can easily be adapted to different situations.
It may, however, also be of interest to analise if the formatting and evaluating functions of Calc are providing means to achieve the goal using less or even none at all of such helper columns.
In fact there is a rather "short" formula relying on only one helper column. Accepting a long formula - and a redundant calculation - it can even be done without a helper column. In specific it is a case where the CURRENT function, so often not powerful enough, is of significant use. Of course, most of the possible side-values of MrProgrammer's solution cannot be achieved this way.

If someone is interested fundamentally they may study the attached example.

[MrProgrammer]

In fact there is a rather "short" formula relying on only one helper column.

You have interesting ideas there. I have tried

=VALUE(LEFT(B11;8)&TEXT(RIGHT(B11;2)/$A$1;$A$2))-VALUE(LEFT(A11;8)&TEXT(RIGHT(A11;2)/$A$1;$A$2))

in D11 instead of

=(VALUE(LEFT(B11;8))+VALUE(RIGHT(B11;2))*$C$4)-(VALUE(LEFT(A11;8))+VALUE(RIGHT(A11;2))*$C$4)

and

=LEFT(TEXT(D11;$A$3);8)&TEXT(VALUE(MID(TEXT(D11;$A$3);9;15))*$A$1;":00")

in C11 instead of

=TEXT(QUOTIENT(D11+1E-016;$C$2)*$C$2;"[hh]:mm:ss")&TEXT((-CURRENT()+D11)/$C$4;":00")

with success, where A2 holds .############### and A3 holds HH:MM:SS.0000000. I don't seem to need the 1E-016 correction, though I have not done extensive testing.

Last evening, lying in bed, I considered using MULTIPLE.OPERATIONS() to avoid helper columns. This attachment shows my implementation, which uses a hidden Converter sheet that can be viewed with Format → Sheet → Show. I still use a helper cell (Converter.C2) but I only need one for the entire spreadsheet, not one per row.

[Lupp]

@MrProgrammer : Your suggested formulae are clearly preferable over mine. And they avoid any call to QUOTIENT and MOD. As I was trained in times when a division was presumed to be expensive, I would suggest in addition to supply 1/NumberOfFramesPerSecond precalculated in a cell, say $C$6, and to replace then the /$A$1 part in your first formula by *$C$6.

I also have to confess a crime: I used "[hh]" as part of the format codes calculating the duration (and thus preparing the check) despite the fact that I cut left parts of fixed length for the conversion to numbers. Allowing for StartFr, StopFr, and DurationFr above 24 h would require inconvenient adaptions in other places, however.

With regard to the MULTIPLE.OPERATIONS idea I can only state that it is very compact and working well. I only "knew" the M~.O~ function and gave it a few tries till now. As I judged it to be too sophisticated a concept, I never actually used it. I will have to learn and possibly to revise my prejudices.

The "+1E-16" was a very inept attempt to "get on the safe side". In fact we cannot reliably fight this way possible round-trip errors in the LSBit of the mantissa of a Double value.

[MrProgrammer]

As I was trained in times when a division was presumed to be expensive …

I was also, and in one period I did assembly language programming on hardware where it might have mattered. But with today's hardware and the lengthy paths between it and the formulas we are using it Calc, I've come to accept that converting division to multiplication is not worth my concern anymore. There are other cases where I would try to optimize the formulas, though, especially using helper cells to avoid redundant calculations. Knowing when to optimize is hard and I encourge most people to write their formulas to make them easy to understand and leave the rest to a machine which probably sits idle 99% percent of the time anyway. Unfortunately, for those of us who remember optimizing code, it is tempting and often fun to consider that, even when it isn't needed.

I only "knew" the M~.O~ function and gave it a few tries till now.

I find it helpful to think of it as a way to write a subroutine using formulas insead of the macro language.