Sum duration times in h:mm:ss

[wiggin]

Greetings Gurus,

I am trying trying to calculate the amount of time students view videos. I understand I should use [h]:mm:ss but excel continues to convert entries such as 0:14:59 (0 hours, 14 minutes, 59 seconds) to 12:14:49 PM both in the cell and the function bar.

The problem gets complicated because I have entered the data with two different formats; some in h:mm and some in h:mm:ss'
1) Is there an easy way to sum the duration time so it accurately reflects total hours:minutes:seconds?
2) Do I need to reformat or re-enter data to make it work?
3) If so, is there a macro to do it?

This has been a bear, (40 students by 30 videos) all hand coded. There's gotta be a better way.

Thanks!

[BadlySpelledBuoy]

Could you post some sample data in a worksheet?

[wiggin]

Thanks for responding BadlySpelledBuoy,

See the attached for a partial data sample

Notice these are not start and end times, they are total duration of viedo viewing in hours minutes seconds. Some cells are formated hh:mm some are hh:mm:ss

I've been told I must convert to decimal minutes to analyze the data in a different statistical package. In any case, it looks like I have to reenter all data in a consistent hh:mm:ss format.

I have my work cut out for me.

[kspeese]

If cell A1 is your start Time, and B1 is your End time:

=TEXT(B1-A1,"h:mm:ss")

[BadlySpelledBuoy]

Have a look at the attached. Simply a case of altering the cell formatting (notes included in the file).
No re-entering of data required.

If this is not what you need, or you require help changing the data to another format for further analysis elsewhere, let me know.

EDIT: Row 3 is just a carbon copy of row 2 to show you that simple re-formatting works. If you format row 2 the same you'll get the same result so it's not like you have to recreate new rows for each student.

BSB.

[wiggin]

Thanks BSB but . . .

See the attached for notes. I just finished re-entering all data by hand, didn't see anyway around it since I had a mix of [h]:mm:ss and h:mm formating.

All is well in the universe ;-)