We help IT Professionals succeed at work.

changing time format in excel

crcsupport
crcsupport used Ask the Experts™
on
I have about 30,000 records in excel having call duration in format hh:mm:ss.
I need to add up all these call duration as minutes for statistics. How can I change this format to number of minutes? I tried to add up just as it is, and it goes over 24 hrs, so it doesn't help.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Gerwin Jansen, EE MVETopic Advisor
Most Valuable Expert 2016

Commented:
You could split your call duration in hours and minutes (forget about the seconds for now) using worksheet functions =hour(field) and =minute(field). Then calculate amount of minutes by: hour*60+minutes, this is a result in minutes per call. Then sum that column and you're done.
Most Valuable Expert 2011
Awarded 2010
Commented:
Hello, add up all the times and format the result with custom format

[mm]:ss

cheers, teylyn
Gerwin Jansen, EE MVETopic Advisor
Most Valuable Expert 2016
Commented:
Assuming your call duration is in column K, the formula in column L would look like this:

=HOUR(K1)*60+MINUTE(K1)

Column L is formatted as number.
Most Valuable Expert 2011
Awarded 2010

Commented:
With over 30,000 records, introducing another column of calculations will add a lot of calculation overhead. The custom format would be much quicker.

Author

Commented:
wow, you guys are the best, i just found it cell * 1400, then change format to general. I came to delete the question, 3 comments already. EE rocks!!!