How to increase timecodes by the same amount in two columns on Excel?

[Ruben79]

Hello,

Thank you in advance for your help.

I have Microsoft Office Professional Plus 2016 and am trying on an Excel spreadsheet to increase the timecodes in two columns by seconds, and tenths & hundredths of a second, for in-and-out music cues on a film.  The amount I need to increase each cell by is 00:00:37;23 and this is the timecode format I'm using is 00:02:40;09 (that's the first cell).

I've tried everything I've seen in my searches but nothing seems to work.  Any help on this is appreciated.

Best,

@Ruben79

[#2]

@Ruben79 

What exactly means the part after semicolon (e.g. 00:29:04;26) - 26 means frames or 26/100 sec or what?

Could you please illustrate in sample file how desired result shall looks like.

[#3]

Hello Sergei,

Thank you for your response. The ;26 mean 26/100 secs. So if the first cell in the screenshot has a value of 00:29:04;26 I need to add 00:000:37;23 to it (and to all the cells in both columns).

Also, the reason I have a semi-colon before the tenths/hundredths of a second is because that's how the editing program (Sony Vegas Pro) indicates those parts of a second (not with a colon or period.) Will the fact that it's a semi-colon be an issue in solving this?

Ruben

[#4]

Hello Herbert,

Thank you for your message. I see in your attachment that you have the tenths/hundredths of a second preceded by a period, not a semi-colon. Does that mean that in Excel a semi-colon can't be used to indicate these values?

Ruben

[#5]

@Ruben79 

Excel use same symbol to separate milliseconds from hh:mm:ss time as to separate decimal part in the number, dot by default.

You actually have texts. To add 25/100 second you need to transform first your text to value, add millisecond and apply proper format. For example, if add to 00:26:41;26 formula could be

=SUBSTITUTE(A1,";",".")+250/24/60/60/1000

[#6]

Just so I'm clear, Excel separates seconds from tenths/hundredths/thousandths of a second with a colon or a period? So would it be this 00:00:00:000 or this 00:00:00.000?

[#7]

@Ruben79 

Milliseconds separated by period.

[#8]

@Ruben79  wrote: ``Just so I'm clear, Excel separates seconds from tenths/hundredths/thousandths of a second with a colon or a period?``

Neither!  It is the same as the "decimal separator" for all numbers.

Typically, that is comma or period, depending on your Regional and Language system options.

But you can override the system default by selecting the appropriate Excel Advanced option.  However, beware that doing that affects the form of __all__ numeric values, no just time formats.

[#9]

Thank you all for your help.

Ruben