# time codes

#### Stuwee

##### New Member
Hi all, I am having a problem with a formula that in my excel days, I think I would have solved.

I have a cell that will have a time code which is always 4 seperate numbers almost acting like a clock

example

10:26:15:15

it stand for hours minutes seconds, and frames

I would like to be able to use this cell and add timecodes to it from other cells giving me the proper result

so, for example if another cell had a value of 00:00:22:16

the result would be

10:26:38:01

keep in mind maximum value for the right hand numbers ( ``the frames`` is 29 ( so at 30 it in fact becomes 00))

the second column, maximum is 59 as is the minutes

hours are irrelavant as timecodes entries aren`t very large.
I thought of using the formula that seperates the fields and uses the rest as a regular clock, but I can`t seem to get that formula to work.

anyhelp is appreciated and there is a post I found here, but for some reason, it is not working, his idea also was to seperate the last value and treat it like a clock.

http://www.mrexcel.com/board2/viewtopic.php?t=128791

If someone was really advanced in this matter, and they like a challenge, then you can consider the following snag of drop frame

simply, it means that unless a timecode is exactly at a 10 minute mark like:
10:20:00:00
11:30:00:00
10:50:00:00

and so on,
there can be no 00, so in fact the time code of
10:03:59:29
becomes 10:04:00:02 immediately after.
there does not exist a 10:04:00:00 or a 10:04:00:01

and this is true for every 9 out of 10 minutes

this is called drop frame and has existed since color television came about and is just a way of making sure timecode reflects real time, otherwise, it would be off a bit because tv broadcasts at 29.97 frames per second and not actually 30 frames per second, so these 2 drops of a frame per minute 9 out of 10 times rebalances the formula to be exact.

I thank you in advance for any help in this matter

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

#### facethegod

##### Well-known Member
Book2
ABCD
110:26:15:1510:26:38:01
200:00:22:16
Sheet1

Try this formula confirmed w/ ctrl + shift + Enter
Code:
``=TEXT(SUM(LEFT(TRIM(A1:A2),LEN(TRIM(A1:A2))-3)+0)+MIN(1/86400,INT(SUM(RIGHT(TRIM(A1:A2),2)+0)/30)/(86400)),"hh:mm:ss")&IF(SUM(RIGHT(TRIM(A1:A2),2)+0)>30,":"&TEXT(MOD(SUM(RIGHT(TRIM(A1:A2),2)+0),30),"0#"),":"&SUM(RIGHT(TRIM(A1:A2),2)+0))``

#### Stuwee

##### New Member
what do you mean ctrl shift enter ?

#### facethegod

##### Well-known Member
For this type of formula (array formula) when you enter it you must hit ctrl + shift + Enter-Not just enter.

HTH

Replies
2
Views
136
Replies
0
Views
169
Replies
1
Views
216
Replies
3
Views
242
Replies
2
Views
264

1,181,657
Messages
5,931,261
Members
436,785
Latest member
KingGideon

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back