# Thread: Rate and Duration (EP PRO SP')

1. ## Rate and Duration (EP PRO SP')

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

2. ## 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").

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.

3. ## 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. ## 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.

5. ## 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. ## 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. ## 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.

8. ## 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.

#### Posting Permissions

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