Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
    
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
add comment

4 Answers

up vote 5 down vote accepted

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.

share|improve this answer
    
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
add comment

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

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

share|improve this answer
    
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
add comment

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
share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.