Having problems with a function that counts seconds in a time sequence

[pglufkin]

Can't seem to overcome a barrier in counting seconds for time sequences that will never go over 10 datapoints. Have a user defined function in my spreadsheet which gives me locations of my semi-colons >>> https://www.dropbox.com/s/n4q4e8jfbt5hb4e/example.xlsm

Sample time sequences (mm:ss) with semi-colon separating time sequences:

#1 24:23; 24:26-24:27; 24:37-24:39; 24:17-24:18; 10:39; 24:40-24:41; 24:28 >>> Calculates 12 seconds, CORRECT!

#2 13:19-13:23; 13:14-13:16 >>>>> Calculates 6 seconds, WRONG, s/b 8 seconds

#3 17:22-17:37 >>>>>> Calculates 16 seconds, CORRECT!

#4 12:12 >>>>>>> Calculates 1 second, CORRECT!

#5 12:25; 12:28; 12:30 >>>>>>>> Calculates 3 seconds, CORRECT!

#6 17:22-17:37; 17:40 >>>>>> Calculates 17 seconds, CORRECT!

My problem is, when I have more than a one second time sequence as my last time sequence; when there are more than one time sequences, it evaluates it as one second. Can anyone help me?

[TMS]

Upload the workbook to the forum. Some people, me included, won't download from external links.

Regards, TMS

[pglufkin]

Ok, thank you.

[AlKey]

Question: how did you come up with 8?

#2 13:19-13:23; 13:14-13:16 >>>>> Calculates 6 seconds, WRONG, s/b 8 seconds
When 13:23-13:19=0:04
13:16-13:14=0:02

[pglufkin]

because it is inclusive . . . 13:19, 13:20, 13:21, 13:22, 13:23, 13:14, 13:15, 13:16 = 8

dash is not a minus sign, it is a continuation sign . . . i want to count the seconds represented

[TMS]

Maybe this function instead:

Please Login or Register  to view this content.

Regards, TMS

[pglufkin]

Thank you very much! Works awesome!

[TMS]

You're welcome. Thanks for the rep.


Bit shorter, maybe?



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


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

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


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[TMS]

Note that the function is not limited to 10 data points.


And this variation copes better with "bad" data, that is, it doesn't generate #VALUE! errors if the data is missing the colon or has unexpected characters.

It also has the option to not be inclusive, should that be a requirement ... but mainly to address AlKey's question in post #4.

Please Login or Register  to view this content.