Formula to calculate time

[alcurtis]

Hi,

I'm trying to get a formula to calculate the time in seconds to the third decimal place from the following data.

It's an export from a video editing program.



The marker start time is calculated by dividing the number after the first " by the number after the /

eg, on the first line reads marker start="29/15s" - result is 1.933

It's duration is calculated by doing the same process on the line below marker start="56/15s" - result is 3.733

Duration is obviously the difference between the two.

I've added three columns on the end to indicate how I would like the result to display. 1st column is Start Time, 2nd is Duration, 3rd is End Time


Thanks very much in advance,



Alan

PS I have attached the excel spreadsheet - Thanks to Pete for the tip



marker start="29/15s" duration="100/3000s" value="Marker 102"/> 1.933 1.800 3.733
marker start="56/15s" duration="100/3000s" value="Marker 103"/>
marker start="83/15s" duration="100/3000s" value="Marker 3"/>
marker start="106/15s" duration="100/3000s" value="Marker 4"/>
marker start="41/5s" duration="100/3000s" value="Marker 5"/>
marker start="281/30s" duration="100/3000s" value="Marker 6"/>
marker start="49/5s" duration="100/3000s" value="Marker 100"/>
marker start="298/15s" duration="100/3000s" value="Marker 7"/>
marker start="316/15s" duration="100/3000s" value="Marker 8"/>
marker start="131/6s" duration="100/3000s" value="Marker 9"/>
marker start="137/6s" duration="100/3000s" value="Marker 10"/>
marker start="709/30s" duration="100/3000s" value="Marker 11"/>
marker start="122/5s" duration="100/3000s" value="Marker 12"/>
marker start="379/15s" duration="100/3000s" value="Marker 13"/>
marker start="79/3s" duration="100/3000s" value="Marker 14"/>

[Pete_UK]

To avoid contributors having to type that sample data into a blank sheet before they can even start to work out a solution, you will get a better response if you attach a sample Excel workbook with the data already in it - the FAQ describes how you can attach a file to one of your posts.

Pete

[alcurtis]

Thanks Pete,

I have attached the spreadsheet

Much appreciated,

Alan

[Pete_UK]

It would appear that your data starts on row 32 and you want to get data from alternate rows, i.e. for markers 102/103, then 3/4, and 5/6. However, your 7th entry on row 38 has a Marker of 100, but there is no corresponding Marker 101 - instead we then have the pairs 7/8, 9/10 and so on. I can see that this will cause difficulties with a formula which is using even-numbered rows to determine the start time and odd-numbered rows to determine the end time. Is this typical of your data, or is this just an erroneous entry?

Pete

[alansidman]

OP has reposted in Commercial Forum. This thread will be closed and OP will continue in the Commercial Forum