Forum Discussion

Richelle Grajo's avatar
Richelle Grajo
Icon for Microsoft rankMicrosoft
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
Richelle Grajo's avatar
Richelle Grajo
Icon for Microsoft rankMicrosoft
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's avatar
Richelle Grajo
Icon for Microsoft rankMicrosoft
May 08, 2018

I was able to use your formula..   Thank you so much!!

  • Richelle Grajo's avatar
    Richelle Grajo
    Icon for Microsoft rankMicrosoft
    May 08, 2018

    Hi,

    Instead of calculating the years for today, how do I calculate the years for June 2018?

     

    Thank you,

     

     

     

    • Haytham Amairah's avatar
      Haytham Amairah
      Silver Contributor
      May 08, 2018

      Simply, replace this:

      =DATEDIF(B2,TODAY(),"y")

      With this:

      =DATEDIF(B2,DATE(2018,6,1),"y")

       

      Regards

      • Richelle Grajo's avatar
        Richelle Grajo
        Icon for Microsoft rankMicrosoft
        May 08, 2018

        Thanks again.  It worked.  

  • Haytham Amairah's avatar
    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 https://support.office.com/en-us/article/use-slicers-to-filter-data-249f966b-a9d5-4b0f-b31a-12651785d29d.

     

    Regards

Resources