+ Reply to Thread
Results 1 to 11 of 11

Date Filter Dropdown - Some in Expandable Year->Month->Day format; others just mm/dd/yyyy

  1. 06-03-2013, 05:47 PM #1
    ashakespeare
    ashakespeare is offline
    Registered User
    Join Date
    08-03-2012
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2010
    Posts
    16

    Question Date Filter Dropdown - Some in Expandable Year->Month->Day format; others just mm/dd/yyyy

    Hello All,

    Having a strange problem with Excel. I have a table with a column of dates. When I click on the filter dropdown, some of the dates are grouped in this format (year and month is expandable):

    2014
    January
    03
    08
    March
    21
    23

    But then some other dates are just on the bottom like this:
    01/26/2014
    08/11/2013

    Why is excel grouping some of the dates and not others? I have formatted the entire column as Short Date (doesn't fix it).

    Thanks!!
    Reply With QuoteRegister To Reply
  2. 06-03-2013, 06:14 PM #2
    Roger Govier
    Roger Govier is offline
    Valued Forum Contributor
    Join Date
    07-17-2005
    Location
    Abergavenny, Wales, UK
    MS-Off Ver
    XL2003, XL2007, XL2010, XL2013, XL2016
    Posts
    608

    Re: Date Filter Dropdown - Some in Expandable Year->Month->Day format; others just mm/dd/y

    Hi

    Can you upload a copy of your workbook?
    Then we might be able to figure it out
    --
    Regards
    Roger Govier
    Microsoft Excel MVP
    Reply With QuoteRegister To Reply
  3. 06-03-2013, 06:19 PM #3
    ashakespeare
    ashakespeare is offline
    Registered User
    Join Date
    08-03-2012
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Date Filter Dropdown - Some in Expandable Year->Month->Day format; others just mm/dd/y

    Please see attached (column C filter):

    date issue.xlsx
    Reply With QuoteRegister To Reply
  4. 06-03-2013, 07:01 PM #4
    jubiesxl
    jubiesxl is offline
    Registered User
    Join Date
    05-17-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    49

    Re: Date Filter Dropdown - Some in Expandable Year->Month->Day format; others just mm/dd/y

    You could be importing your data from elsewhere causing a compatibility issue

    I noticed that you have about 8-10 dates that are not looping into the filter by month and year.

    Select those items and double click to edit and at the start of the string press backspace and the date format will then kick in
    Reply With QuoteRegister To Reply
  5. 06-03-2013, 07:24 PM #5
    ashakespeare
    ashakespeare is offline
    Registered User
    Join Date
    08-03-2012
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Date Filter Dropdown - Some in Expandable Year->Month->Day format; others just mm/dd/y

    Thank you! That fixed my problem
    Reply With QuoteRegister To Reply
  6. 06-04-2013, 04:03 AM #6
    Roger Govier
    Roger Govier is offline
    Valued Forum Contributor
    Join Date
    07-17-2005
    Location
    Abergavenny, Wales, UK
    MS-Off Ver
    XL2003, XL2007, XL2010, XL2013, XL2016
    Posts
    608

    Re: Date Filter Dropdown - Some in Expandable Year->Month->Day format; others just mm/dd/y

    Hi

    Some of your dates are formatted as Text.
    Just changing the formatting of the cell, will not cause them to be formatted as dates.

    In another column (I used column G) enter the following formula and copy down to the last row of your data
    =IF(C2="","",IF(ISNUMBER(C2),C2,DATE(RIGHT(C2,4),MID(C2,4,2),LEFT(C2,2))))

    Then, if you copy from G2 to end, and Paste Special>Values to C2, all of your values will be true dates.

    You would then need to alter your formula in column B from
    =IF(ISBLANK([@[Move Date]]),"No Date",ROUNDUP(([@[Move Date]]-41455)/7,0))

    to
    =IF([@[Move Date]]="","No Date",ROUNDUP(([@[Move Date]]-41455)/7,0))
    Reply With QuoteRegister To Reply
  7. 10-13-2016, 05:28 AM #7
    MariBor
    MariBor is offline
    Registered User
    Join Date
    10-13-2016
    Location
    Turkey
    MS-Off Ver
    2010
    Posts
    2

    Re: Date Filter Dropdown - Some in Expandable Year->Month->Day format; others just mm/dd/y

    Hello, I have the same problem. None of the above worked. can anyone advice pls??
    Some days just wont appears under relevant years. see attachment pls
    screenshot.png
    Reply With QuoteRegister To Reply
  8. 10-13-2016, 05:29 AM #8
    MariBor
    MariBor is offline
    Registered User
    Join Date
    10-13-2016
    Location
    Turkey
    MS-Off Ver
    2010
    Posts
    2

    Re: Date Filter Dropdown - Some in Expandable Year->Month->Day format; others just mm/dd/y

    Hello, I have the same problem. None of the above worked. can anyone advice pls??
    Some days just wont appears under relevant years. see attachment pls
    Attachment 484603
    Reply With QuoteRegister To Reply
  9. 06-12-2017, 05:41 PM #9
    mrmotherloving
    mrmotherloving is offline
    Registered User
    Join Date
    06-12-2017
    Location
    Canada
    MS-Off Ver
    Office 2010
    Posts
    4

    Re: Date Filter Dropdown - Some in Expandable Year->Month->Day format; others just mm/dd/y

    Hi, did someone got back to you or did you fixed it ??? thanks
    Reply With QuoteRegister To Reply
  10. 06-12-2017, 06:49 PM #10
    protonLeah
    protonLeah is offline
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,607

    Re: Date Filter Dropdown - Some in Expandable Year->Month->Day format; others just mm/dd/y

    mrmotherloving,
    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    Ben Van Johnson
    Reply With QuoteRegister To Reply
  11. 06-13-2017, 09:49 AM #11
    mrmotherloving
    mrmotherloving is offline
    Registered User
    Join Date
    06-12-2017
    Location
    Canada
    MS-Off Ver
    Office 2010
    Posts
    4

    Re: Date Filter Dropdown - Some in Expandable Year->Month->Day format; others just mm/dd/y

    Hi,

    Sorry about that, i wasnt aware.
    Plus several other people asked questions and were'nt "flagged". i was just asking the users above if they got their issues fixed.
    oh well...
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1