make by the end of the month.

Amichai.

> Why are you sending these e-mails to me?

>

> R

>

>

> On 1/14/2011 9:09 AM,

[hidden email] wrote:

>

>> I missed the bit saying it's an hourly rate, and assumed minutely rate

>> since you mentioned the time being in minutes; my mistake there. Also used

>> column A instead of B for the time. In that case, putting in cell H2:

>> =B2*24*150

>> will give the cost of the call logged in that line - B2*24 converts the

>> time into a number of hours, then multiplied by your hourly rate (150).

>>

>> Again, I would suggest putting the hourly rate in a separate cell and

>> referring to that, so that it is easy to change. Otherwise, you have to

>> remember to copy the formula down each time the hourly rate is changed. So,

>> with the hourly rate (150 in your example) in cell J1, you'd have in H2:

>> =B2*24*$J$1

>>

>> To get the total in H1, simply add up all the rows:

>> =SUM(H2:H65536)

>>

>> If you don't want the cost of each line itemised, it should be possible to

>> add up the times and then multiply that total by the hourly rate:

>> =SUM(B2:B65536)*24*150

>> or:

>> =SUM(B2:B65536)*24*$J$1

>>

>> Personally, I'd rather have the cost of each line as well as the total

>> though, as it would be easier to track down errors in the data (e.g. if the

>> total is ridiculously large, which line is the problem in).

>>

>> Hope that helps.

>>

>> Mark.

>>

>>

>> Amichai Rotman -

[hidden email] wrote:

>>

>>> Thanks guys,

>>>

>>> But - none of it worked...

>>>

>>> I'll describe my needs again(disregard the last one):

>>>

>>> I have a customer with an hourly rate of 150. Here is what I entered in

>>> the

>>> sheet:

>>>

>>> A1: Date (Header) - Column A formated as Date (DD-MM-YYYY)

>>> B1: Time (Header) - Column B formated as Time (HH:MM)

>>> G1: Subtotal - how much to charge this month (Header)

>>>

>>> A2: The first line with data - the date of the first call this month

>>> B2: The duration of the call - 00:45 (forty five minutes)

>>>

>>> What I want to achieve:

>>>

>>> H1 should show me how much I have earned from this customer till now, so

>>> I

>>> need it to multiply 150 by the time I spent for each line (B2:B32)*150 -

>>> or

>>> something like that...

>>>

>>> Until now I done this with a pocket calculator, for example:

>>>

>>> 0.75 (00:45)+0.25(00:15)+1.25(01:15)*150 would give me the sum of 188.50

>>>

>>> I just want H1 to reflect that sum if I calculate 150 * (B2:B32)

>>>

>>> I hope it is now clearer...

>>>

>>> Thanks!

>>>

>>> Amichai.

>>>

>>>

>>> On Thu, Jan 13, 2011 at 21:33,<

[hidden email]>

>>> wrote:

>>>

>>> Dan Lewis -

[hidden email] wrote:

>>>>

>>>> On Thu, 2011-01-13 at 12:48 +0200, Amichai Rotman wrote:

>>>>>

>>>>> Hey All,

>>>>>>

>>>>>> I have a bunch of customers I give phone support to.

>>>>>>

>>>>>> I'd like to create a spreadsheet to calculate how much I should charge

>>>>>> them

>>>>>> each month. Each customer has a different hourly rate.

>>>>>>

>>>>>> So, here is what I want to do:

>>>>>>

>>>>>> Sheet per customer

>>>>>> For each customer I enter the following:

>>>>>>

>>>>>> Column A: Date

>>>>>> Column B: Time (in minutes - HH:MM, 24hrs format)

>>>>>> Column H: A formula to multiply the contents of B2:B32 times hourly

>>>>>> rate....

>>>>>>

>>>>>> Obviously I need help with that formula...

>>>>>>

>>>>>> Thanks!

>>>>>>

>>>>>> Amichai.

>>>>>>

>>>>>>

>>>>> Have you set up a spreadsheet the way you stated it above? Is

>>>>> Column A formatted for dates? Is Column B formatted for time using the

>>>>> HH:MM format?

>>>>> Format for entering a formula in a cell:

>>>>> 1. Always begin the formula with an equal sign =.

>>>>> 2. Enter A2 after the equal sign.

>>>>> 3. Enter an asterisk after the A2.

>>>>> 4. Enter the hourly rate R followed by the Enter key.

>>>>> Do this in cell H2. Then use the up key to move the outlined

>>>>> rectangle back to H2. The formula in the window should be

>>>>> =A2*R. (Yes, it is this simple.)

>>>>>

>>>>>

>>>> Not quite that simple, unfortunately. If the cells are formatted as

>>>> HH:MM

>>>> (as Amichai says they are), the numeric value of the cell is the

>>>> fraction of

>>>> a day that time represents, not the number of minutes. So what appears

>>>> as

>>>> 00:30 (30 minutes) is actually 0.02083333... (=30/(24*60)) as far as any

>>>> formula is concerned.

>>>>

>>>> =A2*60*24*(Rate_per_minute)

>>>> should do it - A2*60*24 converts the time into a number of minutes, then

>>>> multiplied by your minutely rate. I'm not sure how reliable that would

>>>> be if

>>>> A2 is greater than 24 hours, but that probably isn't an issue to you

>>>> (unless

>>>> you get a lot of very long calls!)

>>>>

>>>> You may want to put the minutely rate in another cell, say J1, and use

>>>> the

>>>> following in H2:

>>>> =A2*60*24*$J$1

>>>> Then you can easily change the rate for each customer. Using $J$2

>>>> (instead

>>>> of just J1) ensures that J1 is used in every line when you copy the

>>>> formula

>>>> to other cells (as Dan describes below).

>>>>

>>>>

>>>> Entering a formula into adjacent cells:

>>>>

>>>>> 1. Place the outlined rectangle in the first cell.

>>>>> 2. Move the cursor over the small square at the bottom right corner

>>>>> (vortex) or the outlined rectangle. The cursor becomes a small plus

>>>>> sign

>>>>> +.

>>>>> 3. Drag and drop the plus sign + to the last cell.

>>>>> This process can be used to copy a formula in a vertical or

>>>>> horizontal range of cells.

>>>>> Begin at cell H2 and drag down to H32. Click any of these cells to

>>>>> see the formula entered into that cell.

>>>>> Another suggestion: Do this for one sheet. Also enter the Headings

>>>>> in cells A1, B1, and H1. Now highlight a block of cells containing

>>>>> columns A through H and rows 1 through 32. A1 should be at the top left

>>>>> of this block of cells, and H32 should be at the bottom right of it.

>>>>> Copy this. In each of the sheets, paste this block of cells into cell

>>>>> A1. You have now formated the sheets and entered the formulas in column

>>>>> H.

>>>>> Now to modify the formulas of each sheet: click cell H2. The

>>>>> formula appears in the window above the column headers. Change the rate

>>>>> to what you want in that sheet and click the green check mark to the

>>>>> left of the window. (It only appears after you change something in this

>>>>> window.) Now drag and drop the formula down to cell H32. Do this for

>>>>> all

>>>>> the cells. You should be good to go.

>>>>>

>>>>> Dan

>>>>>

>>>>>

>>>>> ---------------------------------------------------------------------

>>>>> To unsubscribe, e-mail:

[hidden email]
>>>>> For additional commands, e-mail:

[hidden email]
>>>>>

>>>>>

>>>>>

>>>>>

>>>>>

>>>> ---------------------------------------------------------------------

>>>> To unsubscribe, e-mail:

[hidden email]
>>>> For additional commands, e-mail:

[hidden email]
>>>>

>>>>

>>>>

>>>

>>

>> ---------------------------------------------------------------------

>> To unsubscribe, e-mail:

[hidden email]
>> For additional commands, e-mail:

[hidden email]
>>

>>

>

> ---------------------------------------------------------------------

> To unsubscribe, e-mail:

[hidden email]
> For additional commands, e-mail:

[hidden email]
>

>