How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Ingo U Your Own Question
Ingo U
Ingo U, Software Engineer
Category: Programming
Satisfied Customers: 755
Experience:  Over 25 years experience in software development. Expert in Microsoft .Net, C#, C++, VB, SQL
Type Your Programming Question Here...
Ingo U is online now
A new question is answered every 9 seconds

I'm trying to get a formula to calculate the time in seconds

Customer Question

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.
Thanks very much in advance,
Start Duration End
1.933 1.800 3.733
Submitted: 1 year ago.
Category: Programming
Expert:  Ingo U replied 1 year ago.

Are you looking for an excel formula?

Customer: replied 1 year ago.
were you able to look at the sample?
Expert:  Ingo U replied 1 year ago.

I didn't see a sample file attached, just your text posted here.

You just want a formula that can take a string like

"56/15s" and turn it into a fractional number?

Customer: replied 1 year ago.
Can I upload the excel spreadsheet and send you a link?
Customer: replied 1 year ago.
I've just uploaded the excel fileThanks
Expert:  Ingo U replied 1 year ago.

OH, I see. It's all just one XML document in column A of the sheet then?

That'll take some more doing than a simple formula. I will have to take some time to study it.

Customer: replied 1 year ago.
that's correct.I look forward to hearing from you :)
Expert:  Ingo U replied 1 year ago.

Here's something to head you in the right direction. You can see how the working cells extract the parts of the string needed and convert the numbers.

There are some cases where the input string doesn't have a denominator, you can just add a "/1" after the number manually to get it to work.

Expert:  Ingo U replied 1 year ago.

just following up, were you able to use my solution?
Please feel free to post any additional questions, comments or concerns you may have regarding this issue.

If you're not yet satisfied with the result, please let me know what is missing.
I will do my best to make it right for you.

Otherwise, please don't forget to rate my service - I don't get paid for my work until your rating is received.

Ingo U