+ Reply to Thread
Results 1 to 2 of 2

Pivot table - formula/format to filter by month and year

  1. #1
    Forum Contributor
    Join Date
    04-14-2013
    Location
    Ky
    MS-Off Ver
    Excel 2013
    Posts
    344

    Pivot table - formula/format to filter by month and year

    I have data set that contains several rows of variables, including the start date (mm/dd/yyyy) of that entry. It does not contain separate fields for month and year. I want to be able to filter my pivot table by month and year - so I can view all the data in, for example, October 2013, or just 2014.

    My solution was to create 2 extra columns. In the first column, I had the formula where it was = to the start date cell, but then custom formatted to just "mmm" to display, and in the next column the same except formatted to display "yyyy." But Excel recognized the entire date, not just the month and year (which was displayed), so when I went to filter, it pulled up as many 2014's as were entered in the data with distinct dates.

    Any other solutions?

  2. #2
    Forum Contributor
    Join Date
    04-14-2013
    Location
    Ky
    MS-Off Ver
    Excel 2013
    Posts
    344

    Talking Re: Pivot table - formula/format to filter by month and year

    Ok - just found my answer... for those interested:

    I could use the "Group" function, but I don't have the dates actually in my pivot table... I would have to add the date field and that really seemed to mess things up. I was on track with my thinking to add columns to the source data, just didn't know the formula... I've learned I can use DATE() and YEAR() functions, which will allow me to add my filters to my pivot table.

    BTW, I also don't have slicer, FYI - which looks like it would've been another option...

+ 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. Replies: 10
    Last Post: 06-13-2017, 09:49 AM
  2. How to add Month-on-Month and Year-on-Year %Variance into a pivot table
    By emeritus1812 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 06-09-2013, 02:37 AM
  3. [SOLVED] Sort by Month and year in pivot table
    By mb1074 in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 01-21-2013, 02:05 PM
  4. [SOLVED] Excel 2007 : Month/Year Format with Pivot Table - Excel 2007
    By db1966 in forum Excel General
    Replies: 3
    Last Post: 05-17-2012, 04:35 AM
  5. Trouble with Grouping by Year/Month in Pivot Table
    By mr_bear in forum Excel General
    Replies: 0
    Last Post: 04-13-2010, 03:24 PM

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