Need help with calculations derived from pivot data

[enricotambunan]

Hi All,

My first post to the forum

I'm kinda desperate in creating this excel report and was hoping you guys could help me out.

I have a pivot table having a number of columns - 2 of which I want to create report from is 'Start Date' and 'End Date'.

Question is, how do i getpivotdata from this table, if i were to calculate the period in between 'Start Date' and 'End Date'. Any better way to do this?

I hope im making sense

Thanks in advance.

[benishiryo]

hi enricotambunan, do u need this period in the Pivot? Click on the Pivot, go to the Option Tab -> Formulas -> Calculated Field. Inside the Formula, highlight the whole thing & now double-click your "End Date" in your fields minus "Start Date". it should look like this:
='End Date'- 'Start Date'

[enricotambunan]

Hi Benishiryo,

Thing is, i have tried to do that, but couldnt find the 2 fields on the Fields list. Reason is because these 2 fields are also derived, and not a field with values that we can directly refer to.

Okay, here's the thing... i didnt give you complete information earlier.

I have a table, where 1 Primary key (which is 'Project ID' in this case) is having multiple rows within the table. Each row have a 'Start Date' and 'End Date' value. Hence I used a pivot table to derive:
- For each Project ID, what is the Maximum End Date, and what is the Minimum Start Date (reason is because I need to know the whole duration of that project).

After having that information in a pivot table, I need to calculate the number of days from start to finish.

And now the problem is that I couldnt use a simple calculated field formula to get 'end date'-'start date'.

Any clue?

[benishiryo]

hmmm, would u mind uploading a sample? instead of "Post Quick Reply", click the "Go Advanced" button beside it. Choose the Paper clip icon to attach your file

[enricotambunan]

Here you go...

Sheet 1 is the raw data, and Sheet 2 is my pivot.

I basically need to calculate, for each row item in the pivot table, how many days there are between start date and end date...

Thanks heaps man

[benishiryo]

not sure if this is what u need. u can actually still use what i told u, but your field names are simply different from what i stated:
='For Period Ending'-'For Period Starting'

[enricotambunan]

Hmm.... didnt quite seem to give me correct result though...

Both are starting on 26/1/2012, and ending on 3/5/2012, but both are not saying the same # of days (which are 93 and 101).

Do you know why?

[benishiryo]

damn, there's somethin seriously wrong with me today. this cant work because it took the total sum of ES10159's End Date - sum of ES10159's Start Date. then did the same for the 2nd project.

hmmm i cant think of anything that can work inside the pivot then. a short-term solution is just to put a formula outside your original pivot in H4:
=F4-G4

if you're havin problems with the GETPIVOTDATA, you can either hard-key the formula above or go to your Quick Access Toolbar to add in a "Generate GetPivotData" tool. Uncheck it & u wont have problems using formulas on your pivot.

meanwhile, i can try to think of other solutions. sorry!

[enricotambunan]

damn, there's somethin seriously wrong with me today. this cant work because it took the total sum of ES10159's End Date - sum of ES10159's Start Date. then did the same for the 2nd project.

hmmm i cant think of anything that can work inside the pivot then. a short-term solution is just to put a formula outside your original pivot in H4:
=F4-G4

if you're havin problems with the GETPIVOTDATA, you can either hard-key the formula above or go to your Quick Access Toolbar to add in a "Generate GetPivotData" tool. Uncheck it & u wont have problems using formulas on your pivot.

meanwhile, i can try to think of other solutions. sorry!

I see. Well, let me know if you happen to find the solution

Thanks heaps...

[benishiryo]

i created this little table using formulas. hope it's able to solve your problems?

[enricotambunan]

Thanks for that. But since i need this report to be generated regulary, i need to have this implemented on a getpivotdata (or similar formula). So then the range can be 'dynamic'.

[benishiryo]

u would also need a dynamic range for your pivot in order for u to use your GETPIVOTDATA. so it goes back to using OFFSET formula for my range. if the table i created will help u, then it is just a slight adjustment i need for the range to be dynamic. see if this is ok