Date, Time calculation

[velmurugan7]

Hello,

Can u help me in solving this problem,

There are two columns StartTime, Duration. Format is given below.

1) Add the duration to the start time and place the value in the next row.
2) Duration contains frame as the last value. If the value of frame is 24 then add 1 sec to the start time in the next row.

(A) (B)
StartTime(hh:mm:ss) Duration(hh:mm:ss:frame)
00:40:30 00:00:30:10
00:41:00 00:00:30:14
00:41:31


- Thanks in advance,
Vel

[DonkeyOte]

Vel, not entirely sure I understand -- if we assume your sample represents A1:B4 where A1:B1 = headers... is your formula meant to go into A3 onwards ? I am also confused re: your statement regards Frame -- looking at your sample it seems your final value (00:41:31) has aggregated the Frame of the preceding two duration values such that 1 second has been added (10 + 14) -- else by my reckoning the final value should be 00:41:30, ie:

A4: =A3+(LEFT(TRIM(B3),8))+((--RIGHT(TRIM(B3),2)=24)/86400)

[velmurugan7]

0:40:30(A1) 00:00:30:10(B1)
0:41:00(A2) 00:00:30:14(B2)
0:41:31(A3) 00:00:30:15(B3)

I will enter the values for A1 and B1. Based on those values A2 should be calculated. Again if I enter value for B2, the A3 value should be populated. Apart from normal time addition, I need to add frame. If sum of frame value is 24, 1 sec should be added to next ‘A’ row value

[DonkeyOte]

Excuse my ignorance but what is the range for Frame values ?

If Frame for ex. can exceed 48 in any one given duration value would you add 2 seconds... presumably also you mean sum of time frame value >= 24 add 1 second ?

For ex. consider:

A1: = 00:40:30

Then B1:B6:

00:00:30:10 0
00:00:30:14 1
00:00:30:10 0
00:00:30:10 0
00:00:30:10 1 6 remainder
00:00:30:20 1 20 + remainder: carry over 2

The values to the right of B equate to my question re: sum of FRAME and addition of second requirement...

If you consider also B7:

00:00:30:50

is the seconds here really: INT(sum(frame)/24) ?

What you can do is achievable but we must ensure we have the logic tied down.

[velmurugan7]

Max frame size is 48. Thanks for ur reply.. Its working for me.

[oldchippy]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save