Convert Call Durations to Cost

SheelooC's picture

Let us assume that you have duration of your telephone calls in h:mm:ss format in an Excel sheet;
eg.
00:02:30
00:10:17
1:34:12 ('talking' to your partner after forgetting your anniversary? :-)

Now you want to calculate the cost of each call. Let us assume that phone company charges at $ 0.10 for each 30 second (or fraction thereof) interval.

The following formula (in cell C2) will give you the cost for the time in cell A2 with rate given in cell B2
=ROUNDUP(A2*(24*60*2),0)*$B$2 (Absolute reference to the cell containing the rate will help you copy the formula down)

Explanation;

Excel represents dates as number of days from 1-Jan-1900 so 2-Jul-2013 is represented as 41,457. As a result 24 hours is equivalent to 1.

So to convert duration in h:mm:ss or h:mm you need to multiply it with 24*60 to get minutes.
Multiply it again by 2 you get the multiples of 30 seconds.
Roundup function rounds it up to the next integer to give you number of 30 second 'pulses' used.
To get the cost you multiply by 'cost per 30 seconds'.