+ Reply to Thread
Results 1 to 13 of 13

Date filtering - show cells with text

  1. #1
    Registered User
    Join Date
    01-28-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    8

    Question Date filtering - show cells with text

    Hello -

    I am working on a spreadsheet involving construction dates, and a number of the cells in the date column are written as 'In Progress'. Is there any way to be able to filter for dates after a certain year and not filter out the text-only cells? For example, if I want to show rows where the date is after 2005, this should include any cells with 'In Progress' since those projects will be constructed after 2005, but the way excel is set up now it automatically excludes cells that contain any text from any sort of date filtering I've tried.

    I know the basics of VBA if it's possible to set it up that way.

    Thanks!

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Date filtering - show cells with text

    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    01-28-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Date filtering - show cells with text

    Ok, here is a sample workbook. Hope it makes sense.
    Attached Files Attached Files

  4. #4
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Date filtering - show cells with text

    Why not have a column where In Progress could be shown rather than putting it in the date column. Excel simply doesn't recognize text like In Progress as any kind of date and will not process it as a date.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  5. #5
    Registered User
    Join Date
    01-28-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Date filtering - show cells with text

    The problem is that one of the key purposes of this spreadsheet is that it needs to allow people to be able to search by date through various projects. If I added a column for In Progress, which I've considered doing, what do I put in the 'Date' column that would ensure that whenever they looked for projects that were constructed after a certain date, it would include these 'In Progress' projects?

    I don't need Excel to recognize or process the 'In Progress' text as a date, I was just wondering if there is a way to tell Excel to not hide text no matter what when it's filtering through dates.

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Date filtering - show cells with text

    Taking NewDoverman's suggestion a step further. Put a helper column in that indicates IN PROGRESS. Put an if statement in the Date column to give you today's date if in progress. Filtering would then identify it as an ongoing project.

  7. #7
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Date filtering - show cells with text

    @alansidman: That is a good addition.

  8. #8
    Registered User
    Join Date
    01-28-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Date filtering - show cells with text

    That is a good suggestion. But I have one more idea - is there any way to make it such that if a cell contains today's date (using the = TODAY () function), then it will show the text 'In Progress'? And then when I filter, excel will still process that cell as containing today's date?

  9. #9
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Date filtering - show cells with text

    You may use conditional formatting. Apply a custom number format code:
    "In Progress"
    where the cell value equals =TODAY()
    • Please remember to mark threads Solved with Thread Tools link at top of page.
    • Please use code tags when posting code: [code]Place your code here[/code]
    • Please read Forum Rules

  10. #10
    Registered User
    Join Date
    01-28-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Date filtering - show cells with text

    @Izandol: thanks so much! could you help me a bit more with how to do that? I haven't used conditional formatting a lot before and I'm having a hard time figuring out how to do what you've suggested.

  11. #11
    Registered User
    Join Date
    01-28-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Date filtering - show cells with text

    Never mind I figured it out! Thanks again for the help everyone!

  12. #12
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Date filtering - show cells with text

    Another variation that might work is apply Conditional formatting only when the In Progress column is occupied by In Progress.

  13. #13
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Date filtering - show cells with text

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ 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. CF to colour cells that show a date approaching todays date
    By alexander.small in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-24-2013, 05:23 AM
  2. [SOLVED] Filtering out text from cells?
    By GIS2013 in forum Excel General
    Replies: 2
    Last Post: 07-28-2013, 06:06 AM
  3. Replies: 3
    Last Post: 05-11-2012, 05:11 PM
  4. Replies: 1
    Last Post: 04-27-2012, 07:24 AM
  5. on filtering show whole merged cells
    By carlosmaldonado in forum Excel General
    Replies: 1
    Last Post: 03-18-2011, 11:22 AM

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