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
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
