Work with hours and minutes in Excel 2007

Question

I'm working on a spreadsheet that holds phone calls durations. So, each call (row) has a value like 00:01:30, 01:02:12, etc.

When I sum the duration column, it will give me something like 687:23:42. That's OK.

But, my carrier charge us by minutes. So, I need to convert the above total to minutes. How do I do this?

If I use, for instance, =hour(a1) it will return Excel's "internal" number (I don't know in which format it controls), something like "15"...

I achieved to convert it to minutes by formatting it using [m]. In this example it will return 41243. But, if I try to use this number, it will fail, since it's only a format... (ex.: if I multiply the formated number -- 41243 -- by 10 (for instance), it will give me 150, not 412430, because (i think) the "real" number is 15...

So, how could I achieve this? Since I'm dealing with thousands of lines, I'm looking for a formula. I don't want to do it manually.

Any advice?

Answer 1

Excel stores times as a decimal part of the day, so 1 hour=1/24=.04167

To convert that number to minutes multiply by 1440. 1440 is derived by multiplying the number of hours in a day (24) times the number of minutes in an hour (60).

So the 687:23:42, which equals 28.64 days, multiplied by 1440 = 41,243.70 minutes.

Answer 2

it will give me something like 687:23:42.

Right click the cell, Format, Custom format and type [m]

Answer 3

Make sure your cell is formatted to General or Number and use this formula:

=HOUR(B1-A1)*60+MINUTE(B1-A1)+SECOND(B1-A1)/60

Answer 4

In my city, phone calls are charged in this way:

TOTAL = SUM ( ROUNDUP(duration of each call, minute) )

instead of

TOTAL = ROUNDUP( SUM(duration of each call), minute )

I guess it is more or less the same around the world.

---

That's why, I would suggest the following method for calculating the total minutes used:

To convert the time format to numeric, you can use:

=A1 * 24 * 60

(Assume A1:A10 stores the duration of each call, like 00:01:30, 01:02:12, etc)

Enter into B1 this array formula:

=SUM(ROUNDUP(A1:A10 * 24 * 60, 0))

Press CTRL+SHIFT+ENTER after entering the formula.

With data 00:01:30, 01:02:12, you will get result of 65 minutes (instead of 64).