Sex Call Cost Calculator

johninbrisbane · Mar 5th, 2002, 07:59 PM

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 · Mar 5th, 2002, 08:19 PM

Quote:

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 · Mar 7th, 2002, 07:20 AM

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 · Mar 7th, 2002, 07:32 AM

Quote:

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)

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 5th, 2002, 07:59 PM	#1
johninbrisbane New Member Join Date: Mar 2002 Location: John Posts: 2	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

Mar 7th, 2002, 07:20 AM	#3
johninbrisbane New Member Join Date: Mar 2002 Location: John Posts: 2	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