+ Reply to Thread
Results 1 to 13 of 13

Quick Pivot Table Help - Count and group Multiple Dates

  1. #1
    Registered User
    Join Date
    06-01-2014
    Posts
    79

    Quick Pivot Table Help - Count and group Multiple Dates

    Hi I have a question regarding pivot table and grouping. I have a table that displays every employee along with their hire date and termination date (if applicable). I want to know how I can count how many people were hired/fired and group them by month. I have attached a sample with the expected results highlighted. Problem I keep getting is I can't manipulate the rows in the pivot table to show just months and have the columns populate independently.

    Count of Employee Dates.xlsx

    Any help is greatly Appreciated

  2. #2
    Registered User
    Join Date
    10-10-2014
    Location
    India
    MS-Off Ver
    2010
    Posts
    41

    Re: Quick Pivot Table Help - Count and group Multiple Dates

    Have updated the pivot.
    Attached Files Attached Files
    Vidya Palani

  3. #3
    Registered User
    Join Date
    06-01-2014
    Posts
    79

    Re: Quick Pivot Table Help - Count and group Multiple Dates

    This does not work. This is only grouping the rows by the hire date. I have no termination dates in january but there is still showing "1" under count of termination dates.

  4. #4
    Registered User
    Join Date
    06-01-2014
    Posts
    79

    Re: Quick Pivot Table Help - Count and group Multiple Dates

    I believe that it is counting IF there is a termination date for that hire date but not categorizing it by the date itself. Do I need to create a calculated field?

  5. #5
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Quick Pivot Table Help - Count and group Multiple Dates

    You'd need to change the data structure so your three columns were: Employee, Date, Action (Hire/Terminate). Then the pivot table would have only one date field and you could group it.
    Remember what the dormouse said
    Feed your head

  6. #6
    Registered User
    Join Date
    06-01-2014
    Posts
    79

    Re: Quick Pivot Table Help - Count and group Multiple Dates

    With that example would i need two rows for each employee if they have a termination date?
    EG
    Employee Date Action (Hire/Terminate)
    A 7/10/14 Hire
    A 9/21/14 Terminate

  7. #7
    Registered User
    Join Date
    06-01-2014
    Posts
    79

    Re: Quick Pivot Table Help - Count and group Multiple Dates

    Is there anyway I could keep each employee to have just one row?

  8. #8
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Quick Pivot Table Help - Count and group Multiple Dates

    Not if you want to use a pivot table for this. Why do you need to keep them on one row?

  9. #9
    Registered User
    Join Date
    06-01-2014
    Posts
    79

    Re: Quick Pivot Table Help - Count and group Multiple Dates

    I have other information I want to put in the row. I want to calculate the tenure of the employee and if there are any notes attached aswell.

  10. #10
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Quick Pivot Table Help - Count and group Multiple Dates

    Then I can only suggest that you don't use a pivot table at all. Use formulas to calculate the information you want.

  11. #11
    Registered User
    Join Date
    06-01-2014
    Posts
    79

    Re: Quick Pivot Table Help - Count and group Multiple Dates

    Count of Employee Dates.xlsx
    here's what I have so far with doing formulas. I've gotten it so it sorts them by month. How can I get it to sort by year and then month?

  12. #12
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Quick Pivot Table Help - Count and group Multiple Dates

    This formula in G2:
    =SUMPRODUCT(--(TEXT(Table1[Date Hire],"yyyymmmm")=$E$2&F2))
    will count the data for 2012 January. Copy down for that year.

  13. #13
    Registered User
    Join Date
    06-01-2014
    Posts
    79

    Re: Quick Pivot Table Help - Count and group Multiple Dates

    Perfect! thank you very much

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Group dates in a pivot table slicer
    By lora2014 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 01-02-2014, 01:25 AM
  2. Pivot Group Dates - how to remove start and end dates from table
    By markoloughlin in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 08-21-2012, 04:19 AM
  3. Pivot table group dates
    By RD Wirr in forum Excel General
    Replies: 3
    Last Post: 07-22-2006, 03:10 PM
  4. Group Dates in Pivot Table
    By tufmarkerr in forum Excel General
    Replies: 0
    Last Post: 04-23-2006, 03:42 PM
  5. Pivot table - group dates per week or month
    By digicat in forum Excel General
    Replies: 1
    Last Post: 01-08-2006, 04:50 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1