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?
Best Answer
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.