Time calculation issue with a twist

Hey everyone. I found this place (after many frustrating hours) primarily in an effort to find a solution, but having had a good look around the site, I think I'll stick around. Great site, fantastic support.

I'm facing the same problem as several other people who've posted in the forum regarding the way Excel handles time calculation over 24 hours.

But although I've had a good read, most of the solutions won't work for me because first of all, I'm dealing with audio files and not timesheets etc, so it's important for me to drill down to seconds and frames, not just minutes and hours. (hh:mm:ss:ff or hh:mm:ss:00).

I'm using a Tag Editor to read the duration of each audiofile (among other things) and then I'm using that same program to export the times to a .csv file.

Once inside Excel, I need to perform some calculations on the data, re-format some of the other columns, then re-export the worksheet to be imported by yet another program.

This is another crucial difference. I'm not entering the times manually. I have no control over the length of the file or how the Tag Editor handles the duration. This is where the first part of my problem begins.


When I first open the exported .csv, I get a column that looks like this:

Track# | Length
Trk 01 | 00:44
Trk 02 | 06:32
Trk 03 | 05:12
Trk 04 | 07:20
Trk 05 | 04:19
Trk 06 | 04:26
Trk 07 | 04:02
Trk 08 | 04:05
Trk 09 | 04:01
Trk 10 | 04:31

And what I've tried to do is to set up a 3rd column that calculates the aggregated times (using simple SUM or + functions), like this:

Track#| Length| Running Length
Trk 01 | 00:44 | 00:44
Trk 02 | 06:32 | 07:16
Trk 03 | 05:12 | 12:28
Trk 04 | 07:20 | 19:48
Trk 05 | 04:19 | 00:07
Trk 06 | 04:26 | 04:33
Trk 07 | 04:02 | 08:35
Trk 08 | 04:05 | 12:40
Trk 09 | 04:01 | 16:41
Trk 10 | 04:31 | 21:12


But as you can see (Trk 05), it works great for a while, until I get hit with ye olde 24hr bug. I need to get around this and I need your help.


Ultimately, as well as calculate the times, I'd like it in the correct frame format, like this:

Track#| Length| Running Length
Trk 01 | 00:44 | 00:00:44:00
Trk 02 | 06:32 | 00:07:16:00
Trk 03 | 05:12 | 00:12:28:00
Trk 04 | 07:20 | 00:19:48:00
Trk 05 | 04:19 | 00:24:07:00
Trk 06 | 04:26 | 00:28:33:00
Trk 07 | 04:02 | 00:32:35:00
Trk 08 | 04:05 | 00:36:40:00
Trk 09 | 04:01 | 00:40:41:00
Trk 10 | 04:31 | 00:45:12:00

TOTAL | 45:12 | 00:45:12:00


I'm attaching a freshly exported .csv file in case anyone wants to have a look or write a solution directly to it.

I'd really appreciate some help on this.

Thanks

[TAG]