[Solved] Create timecodes from time

Discuss the spreadsheet application
Locked
ChetWesley
Posts: 3
Joined: Sun Mar 01, 2009 8:06 am

[Solved] Create timecodes from time

Post by ChetWesley »

Hello Everyone,
I have two things I need to work out in Calc. I am sure they can be done, I just don't know the formulas or options to get to them.

SO, I am working with a table with cells like this:

00;07;00
00;27;00
00;55;00
00;59;00
01;01;00
01;06;00
01;12;00
etc.

#1) what I need to do is change each cell to insert a "00;" to the beginning of each series of numbers in each cell... so for example, it would be like
00;00;07;00
00;00;27;00
00;00;55;00
00;00;59;00
00;01;01;00
00;01;06;00
00;01;12;00

NEXT,
#2) I need to generate a column next to that column that has a value of 1 added to the second to last set of numbers, with it rolling over at 60 (this is for timecode in a video, if you are wondering):
00;00;07;00 00;00;08;00
00;00;27;00 00;00;28;00
00;00;55;00 00;00;56;00
00;00;59;00 00;01;00;00
00;01;01;00 00;01;02;00
00;01;06;00 00;01;07;00
00;01;12;00 00;01;13;00
etc.

note the bolded number, where it rolls from 00;00;59;00 to 00;01;00;00

I have around 300 cells to do this too, so if I can get an answer before I finish doing it manually, that will help :)
Last edited by MrProgrammer on Wed Aug 28, 2024 9:56 pm, edited 1 time in total.
Reason: Tagged ✓ [Solved] -- MrProgrammer, forum moderator
OOo 2.0.X on Mac OSx other + Ubuntu
ChetWesley
Posts: 3
Joined: Sun Mar 01, 2009 8:06 am

Re: two challenges for Calc gurus

Post by ChetWesley »

One thing I just thought of... for challenge #2, if it would be easier, I could just add a 24 to the last set of numbers, so for example:
from: 00;00;07;00 to 00;00;07;24

That would avoid having to add the complication of rolling over at 60.
OOo 2.0.X on Mac OSx other + Ubuntu
User avatar
Villeroy
Volunteer
Posts: 31345
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: two challenges for Calc gurus

Post by Villeroy »

You import the values as text from a plain text file or from clipboard, don't you?
And what do the 3 initial figures represent? Hours;Minutes;Seconds?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
ChetWesley
Posts: 3
Joined: Sun Mar 01, 2009 8:06 am

Re: two challenges for Calc gurus

Post by ChetWesley »

Villeroy wrote:You import the values as text from a plain text file or from clipboard, don't you?
And what do the 3 initial figures represent? Hours;Minutes;Seconds?
My values are coming from a pre-existing calc document that someone else made.

In the first group it is minutes, seconds, frames... I need to add the hours to the front of it, because the person who made the list of times didn't include hours because the video isn't over an hour. But I need the hours added for the format used in a script file that uses these numbers.

I have tried using the find all & replace function with a \< wildcard (which "Represents the beginning of a word"), to find only numbers that start with 01; or 02; etc... and have it replace all with 00;01 or 00;02 etc... but the problem is that it will still pick up the "01;" in something like 05;01;00 even though it isn't at the beginning of the group of numbers. The semicolon must throw things off. I had similar problems with using the end of word wildcard ("\>"), it would find things that were in the middle of the group of numbers.

I just remembered that I do not have only 300 lines of this, but more like 1000+ so again, if anyone knows, it will be a great timesaver.
OOo 2.0.X on Mac OSx other + Ubuntu
User avatar
Villeroy
Volunteer
Posts: 31345
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: two challenges for Calc gurus

Post by Villeroy »

Spreadsheets can calculate numbers pretty nicely and apply number formats to the resulting values:
Select the initial triples.
Tools>Text To Columns...
Split by semicolon.
You get 3 columns of numbers, say column A B C, starting in row #1
D1: =TIME(0;$A1+1;$B1)
Calculatates the time value from zero hours, A1+1 minutes and B1 seconds, disregarding the frames. TIME wraps surplus units as well as negative time values properly [TIME(10;-30;0) => 9:30 and TIME(10;90;0) => 11:30]
Once you work with numeric values, formatting is completely up to you.
12:30:00 is just the very same value as
12:30
0.52083 [fraction of a day, all dates and times are formatted numbers in unit "Days"]
or whatever format you like.
But I need the hours added for the format used in a script file that uses these numbers.
Use the TIME formula on another sheet and export to csv:
A1: =TIME(0;$Sheet1.$A1+1;$Sheet1.$B1) [format as you like]
B1: =$Sheet1.$D1 [the frame number]
File>SaveAs...
Type: Text (*.csv)
[X] Edit filter settings
Confirm file format warning (loss of features, ...).
[X] Export as displayed (the formatted time values)
Semicolon as column delimiter.

Code: Select all

00:01:07;0
00:01:27;0
00:01:55;0
00:01:59;0
00:02:01;0
00:02:06;0
00:02:12;0
00:01:00;0
2 fields derived from your semicolon-separated text values. First one is a time, second one is the untouched frame number.
Of course you could add the frame number to the time value as fractions of seconds based on a known frame rate.
Assuming a frame rate 30/sec, you would simply add the frames as a fraction of a day:
=TIME(0;$Sheet1.$A1+1;$Sheet1.$B1)+$Sheet1.$C1/24/60/60/30 and apply a number format such as "HH:MM:SS.00")
and get a single column:

Code: Select all

00:01:07.00
00:01:27.00
00:01:55.00
00:01:59.00
00:02:01.00
00:02:06.00
00:02:12.00
00:01:00.00
If you really want to keep semicolon-separated 2-digit fields for HH;MM;SS;FF
Calculate the time value in the original sheet, say column D in Sheet1 and create the csv values:
A1: =HOUR($Sheet1.$D1)
B1: =MINUTE($Sheet1.$D1)
C1: =SECOND($Sheet1.$D1)
D1: =$Sheet1.$C1
Format all figures with 2 digits (number format "00")

Alternatively:
A1: =TEXT(HOUR($Sheet1.$D1);"00")
B1: =TEXT(MINUTE($Sheet1.$D1);"00")
C1: =TEXT(SECOND($Sheet1.$D1);"00")
D1: =TEXT($Sheet1.$C1;"00")
and set nothing as text-delimiters in the export settings, otherwise you get quoted strings.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Two challenges for Calc gurus

Post by acknak »

My gut reaction is that Calc is not the best tool for this job.

E.g., you can save your data as a text file and run a short perl script like this to do the conversion:
  • $ perl -F\; -ane 'chomp; print("00;$_", "\t", join(";","00", $F[1]<59?"00":"01", sprintf("%02d",($F[1]+1)%60), "00"), "\n")' <hmsf.txt
    00;07;00;00 00;00;08;00
    00;00;27;00 00;00;28;00
    00;00;55;00 00;00;56;00
    00;00;59;00 00;01;00;00
    00;01;01;00 00;00;02;00
    00;01;06;00 00;00;07;00
    00;01;12;00 00;00;13;00
Almost any scripting language would be similar. A more legible version that does correct time arithmetic would still be only a few lines.
AOO4/LO5 • Linux • Fedora 23
Locked