time codes

Stuwee

New Member
Joined
Jul 30, 2007
Messages
2
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
Joined
Aug 1, 2006
Messages
767
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))
 

facethegod

Well-known Member
Joined
Aug 1, 2006
Messages
767
For this type of formula (array formula) when you enter it you must hit ctrl + shift + Enter-Not just enter.


HTH
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top