LinkBack URL
About LinkBacks
Hi,
I want to add a field ,"duration" and calculate duration between Start Date and End Date in pivot table.
Is it possible in pivot table?
If possible, how can I do that?
Thanks
Hi,
I want to add a field ,"duration" and calculate duration between Start Date and End Date in pivot table.
Is it possible in pivot table?
If possible, how can I do that?
Thanks
Yes, you can add CALCULATED FIELDS to a pivot table.
1) Create the pivot
2) Select it
3) PivotTable Tools > Options > Fields, Items & Sets > Calculated Field
Name: Duration
Formula: =End - Start (select the correct field names)
4) Now you can add this newly created field to you Pivot table results like any other field.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Thanks.
I got the raw data and in this raw data , I got Date field. This is timesheet for workers.
I used Date in the Value field to get Min and Max function in pivot.
So, in the pivot table, I have Start Date and End Date in value field. In this case, can't I use ?
Or, could you tell me other way to calculate duration beside pivot?
I am looking up sumif function. Do you think this is good?
Thanks
I'm a little lost. Post a workbook with a SMALL subset of desensitized data showing some of this info and the Pivot you have created from this small data set. Point out there what you're trying to add and I'll take a look.
Remember to desensitize the data.
Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic
Here it is.
Thanks
Gotcha! I see the problem. Yes, Pivot Tables do not inherently allow creation of the =MAX(Date) - MIN(Date) syntax, it just shows zeros everywhere.
But you can install a powerful addon for Excel 2010 (free) called PowerPivot which adds even more wonderful pivoting tools, and that is amongst them.
https://www.youtube.com/watch?v=lE2OAPh-Zf8
I am going to give my Excel Template to other users to use.
Are they need to install Power Pivot too to use?
Thanks
Yes. Anyone with Excel 2010 or great should install it.
I mean they have to install to use my template? otherwise they get an error??
Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks