I've got a big csv with head movement data that I need to combine with an audio recording. The timecode in the csv doesn't start at 0, so I need to subtract the starting time from all entries.

The starting timecode looks like: 00:36:16:15.295

It's a bit confusing, I'm not certain what each number signifies. They all go to 59, except the part after the dot, which goes to 999.

How do I make the column start at a timecode of 00:00:00:00.000?

CodePudding user response：

This is not a satisfying answer, but it is a solution to the problem at hand:

Using Data > Text to Columns... I split the timecode 00:36:16:15.295 into four separate columns: 00 | 36 | 15 | 15.295.

I then deleted the last column and created a new column with the following formula: =TIME(A3,SUM(B3,-36),SUM(C3,-16)). Using Format > Cells... I turned the resulting values into a time.

CodePudding user response：

Are you sure about what you are saying?
You have the following timestamp:

00:36:16:15.295

This is of the form xx:xx:xx:xx.xxx.
Most timestamps are of the form hh:mm:ss.sss, but you seem to have an extra one, so seem to think that it is of the form dd:hh:mm:ss.sss (where d stands for "days"). But then you say that your amount of hours might go up to 59, which makes no sense.
So I would think that you are dealing with the form hh:mm:ss:ss.sss, where ss:ss.sss stands for seconds, hundreds of seconds, and hundred thousands of seconds (it's just a case of extreme accuracy).
This would explain why the first entry might go up to 59.
It also means that the second last entry (15 in your case) might go up to 99, instead of 59. Can you check if this is true?