Convert cummlative hh:mm:ss to minutes

[dtjd1993]

I have a report that lists the amount of time people are logged out of a call center for various reasons. The time is cummulative for the day. I would like to add up the cummulative times for each reason and person for a month but the format is hh:mm:ss. I need to convert this to minutes. For example someone may have been logged out for 1 hr, 28 minutes and 30 seconds. The cell shows 01:28:30. I would like to convert this to 88.5 minutes. I have 1000's of entries that need to be "summed" I am looking for a formula to do the conversion for me. Any ideas?

 

[shonaart]

Hi,
Try format it as [H]:mm and sum it.
Hope this helps

 

[drsarao]

Welcome to MrExcel

For conversion use:
=HOUR(A1)*60+MINUTE(A1)+SECOND(A1)/60. You should format the cell where you entered this formula into Number with 2 decimal.

To "add up the cummulative times for each reason and person for a month":
Some sort of SUMIF/SUMPRODUCT will be required. Sample data will be useful to help further.

 

[mvptomlinson]

Since time is stored as a decimal value between 0 (midnight) and .999 (11:59:59), you can just multiply the time (01:28:30) by 1440 (the number of seconds in a day) to get 88.5 (need to format the cell as number, of course).

 

[drsarao]

Tomlinson,
Thanks for the insight.
Your method also covers for situation where number of summed hours exceed 24.

 

[dtjd1993]

Thank you!

 

[muneshkush]

HI,

I am still unable to convert 132:25:48 (hh:mm:ss) to seconds. Please help..

 

[shg]

      ----A---- --B--- ------C------
  1   132:25:48 476748 B1: =A1*86400
  2             476748 B2: =A1

B1 is formatted as Number, B2 is formatted as

 

[muneshkush]

      ----A---- --B--- ------C------
  1   132:25:48 476748 B1: =A1*86400
  2             476748 B2: =A1

B1 is formatted as Number, B2 is formatted as

thanks a lot SHG.. It really helped..

 

[STEMALO]

I have a similar issue.
i need to take a start time (formatted in yyyy-mm-dd h:mm) and end time (formatted in yyyy-mm-dd h:mm) and calculate the difference. The end result should be given in hours and decimal minutes.

ex:
cell A1 = 2012-02-11 8:10
cell B1 = 2012-02-11 12:05
I would do A1 - B1 and the result is formatted as [h]:mm
i then take mm and manually divide it by 60
the result is the merged (manually) to the [h]
in this particular example the answer that i would like to get by simply using 1 formula would be 3.92.

any help is greatly appreciated