+ Reply to Thread
Results 1 to 7 of 7

Forumula generated date column not sorting with filter

  1. #1
    Registered User
    Join Date
    12-17-2012
    Location
    South Korea
    MS-Off Ver
    Excel 2007
    Posts
    4

    Forumula generated date column not sorting with filter

    I am trying to sort a sheet (ART 15 report) based on a date column where the date entries are really just copied from a separate sheet (ART 15 log) using this formula: =IF('ART 15 log'!I52="","",'ART 15 log'!I52). I assume the issue is Excel is sorting the formula, not the date. Anyone know a fix for this? Ideally, I would like to sort one sheet (ART 15 log) and have that sort simultaneously be applied to the second sheet (ART 15 report). If that's not possible, is there any way to convince Excel to sort by date and not formula on my report sheet?

    Thank you!

  2. #2
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Forumula generated date column not sorting with filter

    I think Excel will sort the date not the formula. Check on the default date format MM-DD-YYYY or DD-MM-YYYY so the dates entered follow the default date format. Let's solve this before going to next step.

  3. #3
    Registered User
    Join Date
    12-17-2012
    Location
    South Korea
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Forumula generated date column not sorting with filter

    That was my first thought, but I have the date format set for DD-MMM-YY. Have tried other formats such as mm-dd-yy with the same results. When I apply filter it only gives me the option to sort a-z and by color.

  4. #4
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Forumula generated date column not sorting with filter

    I tried both filtering and sorting a bunch of dates and in both cases it gives me the option oldest-newest so check if the column is set to DATE and all are valid dates.

  5. #5
    Registered User
    Join Date
    12-17-2012
    Location
    South Korea
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Forumula generated date column not sorting with filter

    That's the problem, they are not really dates, but formulas that correspond to dates on another sheet. The dates are visible on the sheet I want to sort, but this code (=IF('ART 15 log'!I52="","",'ART 15 log'!I52)) is what I suspect is being sorted instead. My question is can I sort using filter or some other method when the date is not actually typed into the sheet I'm trying to sort. I can sort the sheet on which I typed the dates. Then, the question is, if I can't sort on this sheet, is there a way to sort on the other sheet and have this sheet automatically sort? Confusing?

  6. #6
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365
    Posts
    12,817

    Re: Forumula generated date column not sorting with filter

    More than likely, you do not have real dates but text looking like dates. Therefore they will not sort as dates should. This has nothing to do with some "sorting" of formula.
    Perhaps post a sample of your data

  7. #7
    Registered User
    Join Date
    12-17-2012
    Location
    South Korea
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Forumula generated date column not sorting with filter

    Not sure what, but somewhere along the line something I did fixed the problem. Thanks for the help on this.

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1