# Work with hours and minutes in Excel 2007

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.

-
OK, so I didn't see the last part - I'm not sure I understand this - `But, if I try to use this number, it will fail, since it's only a format` - how are you trying to use it ? Basic arithmetic seems to work for me. – Sathya Sep 29 '10 at 18:39
I edited the post tring to explain it better. Since the "real" number is 15, when I use (for instance multiply it by something), Excel's will use the underling number (15) not the number that I'm seeing (the formated one)... – Bob Rivers Sep 29 '10 at 18:51

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.

-
Nice to know... I also achieved the same number by using this formula: =day(a1)*24*60+hour(a1)*60+minute(a1).... Of course that your response explains why it works this way... – Bob Rivers Sep 29 '10 at 19:31

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

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

-
OK, so I didn't see the last part - I'm not sure I understand this - `But, if I try to use this number, it will fail, since it's only a format` - how are you trying to use it ? Basic arithmetic seems to work for me. – Sathya Sep 29 '10 at 18:38

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
``````
-

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

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

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).

-