Jump to content


Photo

Pivot Table

How to add date in value area

  • Please log in to reply
3 replies to this topic

#1 Ayaz

Ayaz

    Newbie

  • Members
  • Pip
  • 8 posts
  • LocationSaudi Arabia

Posted 27 October 2014 - 05:37 PM

Greetings,

 

Is it possible to add date in value area in pivot table?

 

Regards



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 845 posts

Posted 27 October 2014 - 09:30 PM

Hi Ayaz,

 

It is possible to use dates as values in pivot tables, but they will of course be treated as their date serial number values, so this is probably not very useful.  If you're not familiar with the concept of date serial numbers, you can find a full explanation of them in the video lesson: Expert Skills Lesson 3-7 Understand Excel date serial numbers and custom date formats.

 

Is there something specific you are trying to do with your dates?


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#3 Ayaz

Ayaz

    Newbie

  • Members
  • Pip
  • 8 posts
  • LocationSaudi Arabia

Posted 29 October 2014 - 07:03 PM

Thanks for reply, I see serial numbers, works but not fully yet.

I have data showing for example invoices under different projects, which has three columns with date, date of invoice, date of payment if paid, expected date of payment which varies under different contract, so in this case date becoms vital to show in pivot table.



#4 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 845 posts

Posted 30 October 2014 - 07:19 PM

Hi Ayaz,

 

There are a number of different techniques for using dates within pivot tables.  I'm not quite sure exactly what you need, but you may find the following techniques useful:

 

Grouping By Date

 

pivotdates1.png
 

You can use grouping to create pivot tables that summarize your data by Days, Months, Quarters or Years, which is very useful in many pivot tables.  You can see how to do this in the video lesson: Expert Skills Lesson 5-20 Group by Date.

 

 

Filtering By Date

 

pivotdates2.png
 

If you need a pivot table to only summarize a certain date range, you can achieve this by using report filter fields.  You can see a walkthrough of this technique in the video lesson: Expert Skills Lesson 5-7 Use report filter fields.

 

 

Creating a Date Interface Using Slicers

 

pivotdates3.png

 

You can create a very presentable date selection interface by using Excel's new slicers feature.  For a video lesson showing how to create an interface like this one, see: Expert Skills Lesson 5-10 Use slicers to create a custom timeline.

 

 

If you decide to use one of these techniques and need assistance applying it to your workbook, please feel free to reply, ideally with a copy of your workbook attached, and I will do my best to assist you.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users