[Solved] Timecode difference between IN & OUT - hh:mm:ss:ff

[Cippalippa1975!]

Hi,

I would need to create a cue sheet with the time codes of the music cues in a movie, with hours, minutes, seconds and frames, for example 01:00:00:00

The movie is divided into acts 1, 2 and 3, which start respectively at TC 01:00:00:00, 02:00:00:00 and 03:00:00:00

1) I'd like to format the cells with numbers that look like 01:00:00:00

2) If for example the IN of a cue in ACT 1 is 01:00:29:00 and the OUT is 01:01:01:00, I'd like to use a formula that would show the DURATION as 00:00:32:00. If a similar situation would appear in ACT 3 we would have the IN at 03:00:29:00 and the OUT at 03:01:01:00, and I'd like the duration to show still as 00:00:32:00

3) being frame accurate, according to a frame rate would be awesome, but I'm not really good with formulas so the simpler the better. Having the :00 digits at the end, rather than just hours, minutes and seconds will help to follow more easily the time code burnt in the picture.

Thank you!

[robleyd]

1) I'd like to format the cells with numbers that look like 01:00:00:00

There is an existing format under format category TIME - [HH]:MM:SS.00 - which is almost identical to your needs. Format your start, end and duration cells thus. Then:

2) If for example the IN of a cue in ACT 1 is 01:00:29:00 and the OUT is 01:01:01:00, I'd like to use a formula that would show the DURATION as 00:00:32:00.

You can subtract IN from OUT to obtain DURATION.

[Villeroy]

As far as I know, the last part ff in hh:mm:ss:ff refers to frames which is not the same as hundredth seconds. If you need the precise difference, you need a column for the time and another column for the frames. Any time diff will be calculated as 1 hour, 35 minutes, 45 seconds and 15 frames. A spreadsheet can not recognize 01:35:45:15 as a calculatable numeric value.

[MrProgrammer]

I'm not really good with formulas so the simpler the better. Having the :00 digits at the end, rather than just hours, minutes and seconds will help to follow more easily the time code burnt in the picture.

The formulas when all the timecodes end in :00 are simple. See sheet Frame00 of the attachment.

202108211443.ods

(11.95 KiB) Downloaded 207 times

being frame accurate, according to a frame rate would be awesome

You can do that too, though the formulas are more complex, as you suspected. See sheet Frame24 of the attachment. I used 24 frames per second, though you can change the rate in Insert → Names → Define.

Related topics:
[Solved] Convert hours:minutes:seconds:frames to minutes
[Solved] How to set time to TIMECODE ?
[Solved] User-defined time format (Timecode)
[Solved] Help converting frames to 'HH:MM:SS.FF.00'

[Tutorial] Ten concepts that every Calc user should know

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.

[Cippalippa1975!]

Brilliant! That you so much!!