Forum Discussion

Microsoft

May 08, 2018

Pivot table question

Hello, I need to create a pivot table that includes names and pivot each name based on their service dates for 1 year, 5 year, 10 year, etc. How do I group all the names together within a speci...

excel

Haytham Amairah

Silver Contributor

May 08, 2018

Hi Richelle,

In the attached file, you will find a similar approach to what you are asking for.

This is done by using a Table, not a PivotTable.

Hope that helps

Haytham

Emps Table.xlsx15 KB

Richelle Grajo

Microsoft

May 08, 2018

Yes, this is what I need to do. How do I create an employee table? Do you mind creating steps to accomplish this?

Thanks.

Richelle Grajo
Microsoft
May 08, 2018
I was able to use your formula.. Thank you so much!!
- Richelle Grajo
  Microsoft
  May 08, 2018
  Hi,
  
  Instead of calculating the years for today, how do I calculate the years for June 2018?
  
  Thank you,
  - Haytham Amairah
    Silver Contributor
    May 08, 2018
    Simply, replace this:
    =DATEDIF(B2,TODAY(),"y")
    With this:
    =DATEDIF(B2,DATE(2018,6,1),"y")
    
    Regards
- Haytham Amairah
  Silver Contributor
  May 08, 2018
  Richelle,
  
  You're welcome!
  In fact, that table is a dummy table I created in less than one minute!
  
  In your real table, you have to include the joining date for each employee, and then use the below formula to get the service years based on the joining date:
  =DATEDIF(B2, TODAY(), "y")
  
  After that, you can filter all employees with specific years of service by using a Slicer.
  
  Regards