Time calculation issue with a twist

**theaudiobookguy** · 07-26-2009, 09:07 AM

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]

**daddylonglegs** · 07-26-2009, 09:17 AM

I can't open the file but it looks like your first problem is just a formatting issue, format the cells in the total column like this

[hh]:mm

Note the square brackets which allow display of values over 24 hours

For the second, "hh:mm:ss:ff" isn't a valid excel format but if you don't mind the last : being a . then you could use this formula in B2 copied down (assuming data in A2 down)

=SUM(A$2:A2)/60

format column as hh:mm:ss.00

If you absolutely need it to look like hh:mm:ss:ff then you can use this formula:

=TEXT(SUM(A$1:A1)/60,"hh:mm:ss")&":00"

Note: I'm assuming that the frames are always zero, as per your example, or isn't that the case?

**theaudiobookguy** · 07-26-2009, 10:05 AM

Firstly, thanks for the response.

Originally Posted by daddylonglegs

I can't open the file...

Ack. You can't open the file because I don't have winzip installed and the forum wouldn't accept .rar, so I renamed it to .zip. I hoped it would work.

I did consider saving it as an .xls or something, but I wanted to be sure that you got the same file I get immediately after exporting.

Should have mentioned that before. Sorry. If you can open .rar files, try renaming the extension and it should open.

but it looks like your first problem is just a formatting issue, format the cells in the total column like this

Hmm. total column? Sorry but you've confused me a bit by using a term I hadn't used originally, so I'm unsure which column you're referring to. A, B, or C? (i.e. Trk#, Length, Running Length)

[hh]:mm

Note the square brackets which allow display of values over 24 hours

Aha. I've been wondering what that does exactly. Cheers.

if you don't mind the last : being a .

No. I can live with that. the other programs will accept that format.

If you absolutely need it to look like hh:mm:ss:ff

Yes I do. Some of the other programs will be expecting the digits, even if they're blank. In fact, the only reason they're blank is because the Tag Editor is either simply not sensitive enough to report them or just isn't bothering to (as if it doesn't matter).

But when you open them in a more professional tool, you see that there are indeed frames present. I'll have to live with that for now though because as I say, I have no control over that.

I'm assuming that the frames are always zero, as per your example, or isn't that the case?

No. There are so many frames within a single second, that you can not only see .57, or .89, you can also have .700, .350 etc.

Now, after all that, I'm not even sure I understand your solution. That doesn't mean it's not clear (apart from the 'total' thing throwing me off a bit.

But I've had no sleep since Saturday morning and even then, I'd only had a few hours, so I'm pretty frazzled right now.

I'll do my best to wake myself up and have another look after posting this, but that's why I was hoping someone might be kind enough to have a go at it for me--trust me to upload a file that won't open!!

Cheers

[TAG]

:edit: THANK YOU. THANK YOU. THANK YOU.

Works perfectly; just what I needed. I'm buzzin (but I can't tell if it's from relief, ecstasy or sleep deprivation)

p.s. wasn't sure how to mark my post as solved, so hopefully one of you mods will. cheers