Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Sex Call Cost Calculator

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    John
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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,

    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  3. #3
    New Member
    Join Date
    Mar 2002
    Location
    John
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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)

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •