Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Location
    Glasgow, Lanarkshire
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Rate and Duration (EP PRO SP')

    I need to work out costs using Ex cell if the RATE

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Rate and Duration (EP PRO SP')

    <P ID="edit" class=small>(Edited by Jezza on 01-Oct-06 18:53. Slight change in the multiple )</P>Hi Jimmy (again <img src=/S/grin.gif border=0 alt=grin width=15 height=15>)

    Have you tried =CONVERT(A1,"mn","sec").

    A1 holds your mm:ss
    mn= minutes to convert into sec =Seconds


    Now if you multiply by 1440, you get the total seconds you can now multiply by your rate.
    Jerry

  3. #3
    Star Lounger
    Join Date
    Sep 2002
    Location
    Glasgow, Lanarkshire
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rate and Duration (EP PRO SP')

    Thank's for your super fast reply I just knew it would be something simple. BTW good memory not been here for a long time <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rate and Duration (EP PRO SP')

    No, since your rate is per minute, you want the time in minutes. I will assume that the 10:21 is ten minutes and 21 seconds (not 10 hours and 21 minutes) stored as and Excel time value. Since Excel time values are stored as the fractional part of a day (ie. 6 hours is stored as .25 days), you need to multiply the Excel time value by the number of minutes in a day (24*60=1440) to convert the time value into decimal minutes. Therefore, in your example, 10:21*1440*.105=1.09.
    Legare Coleman

  5. #5
    Star Lounger
    Join Date
    Sep 2002
    Location
    Glasgow, Lanarkshire
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rate and Duration (EP PRO SP')

    Thank you I new there was something odd about Excel time thank you for this formula
    Jimmy

  6. #6
    Star Lounger
    Join Date
    Sep 2002
    Location
    Glasgow, Lanarkshire
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rate and Duration (EP PRO SP')

    I am missing somthing here =SUM(10:21*1440*0.105) gives "00 January 1900

    <img src=/S/confused3.gif border=0 alt=confused3 width=45 height=45> Jimmy

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Rate and Duration (EP PRO SP')

    You have to format your cells Jim:

    Type in:

    =A1*1440*0.105 Where A1 is 10:21

    Then Format | Cells | Number and select number in the category list.
    Jerry

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rate and Duration (EP PRO SP')

    First, you don't need the SUM function. Since it has only one argument which is not a range of cells, there is nothing to SUM. Having it there won't change the answer, and it does slow thing down. So, the formula you need is:

    <code>
    =10:21*1440*0.105
    </code>

    Since that formula contains a time (10:21), Excel assumes that the result of the formula is also a time, so it formats the cell to display a time value. It doesn't realize that multiplying the time value by 1440 converts it to decimal minutes and that the final result is British Pounds. You will need to change the cell format back to General, or Number, or a Currency format AFTER entering the formula and it should then display the correct result.
    Legare Coleman

Posting Permissions

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