Hi Jerry

Email me directly with a copy of your file and I will see if I can sort it out.

Remove NOSPAM from my email address to sen direct.

Regards

Roger Govier


jerry wrote:
> Roger,
>
> thanks for your responses thus far. However,
>
> I guess i am not getting the right info to you. First to answer your
> question. The source time is in decimals(.50 = 1/2 hour).
>
> Next,
>
> I have sumbitted time by various resources.
>
> John smith Task 1(i.e.documenting) - 10/1/05 3 hours 50.00/hr
> Task 2 (i.e. development) - 10/2/05 2 hours 50.00/hr
> Task 3(i.e. development) - 10/3/05 .5 hours 50.00/hr
>
> in the above scenario if i do not include the task name and the date in my
> pivot table,the hours are summarized by resource(john smith) multiplied by
> the rate then multiplied again by the number of tasks for the week.
>
> thus the above would calculate as follows (5.5 * 50) *3 = 825.00 vs
>
> what i am hoping the result would be is that no task and date beign
> required in the pivot table and the result would be = 275.00
>
> hope this add some more clarity. Sorry about the confusion.
>
> thanks
> Jerry
>
>
> "Roger Govier" wrote:
>
>
>>Hi Jerry
>>
>>As I said originally, if the times are 2:00, 3:00 etc., whilst this is the
>>displayed value (either in the source data or the PT summary), Excel stores
>>all time values internally as fractions of a day.
>>
>>To convert to decimal hours, you need to multiply by 24
>>
>>=(2:00+3:00+0:50)*24*50 = 291.6667
>>You need to format the cell with the formula as General.
>>
>>In your example, you show one time as 0.50. Do you mean 0:50 or 50 minutes
>>as opposed to half an hour? If so then the above applies.
>>
>>If you truly mean .50 and your source times are decimal hours then the total
>> would not need to be multiplied by 24.
>>
>>Regards
>>
>>Roger Govier
>>
>>
>>jerry wrote:
>>
>>>Roger, That did not seem to work. Maybe a little more information would help.
>>>
>>>I believe the challenge is the in the way pivot tables summarizes the
>>>multiplies.
>>>
>>>For example
>>>
>>>10/1/05 task 1 = 2.00 hours
>>>10/1/05 task 2 = 3.00 hours
>>>10/3/05t task 3 = .50
>>>
>>>rate 50.00/hour
>>>
>>>thus,(2.00+3.00+.50=5.5 * 50.00) * 3
>>>
>>>
>>>Instead what i am looking for in the above example is 5.5 hours *50.00/hr.
>>>
>>>Any idea, how i can accomplish this?
>>>thanks
>>>
>>>
>>>Jerry
>>>"Roger Govier" wrote:
>>>
>>>
>>>
>>>>Hi Terry
>>>>
>>>>Excel stores hours as fractions of a day, so multiply Hours * 24 * Bill Rate
>>>>
>>>>Regards
>>>>
>>>>Roger Govier
>>>>
>>>>
>>>>jerry wrote:
>>>>
>>>>
>>>>>I am querying an external database to retrun a pivot table, then i am adding
>>>>>a calculated field that calculates Regular hours * Bill rate. to come up
>>>>>with total revenue. When it runs, the calculated amounts are 2, 3, 4 times
>>>>>more that what it should be.
>>>>>
>>>>>any ideas how i can get this calculation to return the right amounts.
>>>>>
>>>>>thanks
>>>>>Jerry
>>>>