Sex Call Cost Calculator

[johninbrisbane]

The problem is simple. I have a list of telephone call durations with colon format in minutes in seconds, e.g. 2:11 or 127:59. What is the easiest way in Office 97 to decimalise these durations so that I can then multiply by a cost per minute to calculate the resultant call cost. Currently I am using FIND and REPLACE to convert ':' to '.' and then using a combination of INTEGER and REMAINDER functions to do this.

I guess that I could use a STRING function to modify the the third character from the right, as opposed to a global REPLACE for the whole column.

I was hoping that MS may already have a built in function to decimalise minutes and seconds - bit I can't locate it.

Regards, John

 

[Barrie Davidson]

On 2002-03-05 18:59, johninbrisbane wrote:
The problem is simple. I have a list of telephone call durations with colon format in minutes in seconds, e.g. 2:11 or 127:59. What is the easiest way in Office 97 to decimalise these durations so that I can then multiply by a cost per minute to calculate the resultant call cost. Currently I am using FIND and REPLACE to convert ':' to '.' and then using a combination of INTEGER and REMAINDER functions to do this.

I guess that I could use a STRING function to modify the the third character from the right, as opposed to a global REPLACE for the whole column.

I was hoping that MS may already have a built in function to decimalise minutes and seconds - bit I can't locate it.

Regards, John

If Excel recognizes your data as time, then use this formula:

=A1*1440*A2

Where A1 is the call duration and A2 is the rate per minute. Note, you'll have to format the cell where you put this formula to a number otherwise Excel will format it as a date.

Regards,

 

[johninbrisbane]

Thanks for the simple answer. For the sake of completeness I guess that the multiplication factor should be 24 if the time based rate is per minute OR 1440 if the factor is per second based.

Regards, John

 

[IML]

On 2002-03-07 06:20, johninbrisbane wrote:
Thanks for the simple answer. For the sake of completeness I guess that the multiplication factor should be 24 if the time based rate is per minute OR 1440 if the factor is per second based.

Regards, John

Actually, since excel stores time as fraction of a day (1=24 hours)
it would be
hours 24
minutes 1440 (60*24)
seconds 86400 (60*60*24)