Pivot Table Calculated Item

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

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
>

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
>>

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
> >>
>

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
>>>>