+ Reply to Thread
Results 1 to 3 of 3

Help! If cell contains any sort of date, show green checkmark. Otherwise, show red...

  1. #1
    Registered User
    Join Date
    02-23-2014
    Location
    Kentucky
    MS-Off Ver
    Excel 2010
    Posts
    7

    Help! If cell contains any sort of date, show green checkmark. Otherwise, show red...

    Hello All:

    I'm just looking for a way that when I type ANY date in the cell, it will produce a green checkmark icon.

    If the cell is blank, I need it to show the red X icon.

    See attached graphic for example.

    Disregard the yellow exclamation.

    By the way, these icons are located under conditional formatting / Icon sets.

    Thanks in advance!
    Attached Images Attached Images

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,739

    Re: Help! If cell contains any sort of date, show green checkmark. Otherwise, show red..

    we can do that if the icons are in the next column and not in the same column as the date
    and you can choose show ICON only

    in B1 put
    =IF(A1="",3,1)
    Now copy down the column B

    set the icons to
    >=2.1
    >=1.1
    and reverse the icons and click on only show icon

    this will not test for a date - anything in the cell other than a blank will be a tick
    Attached Files Attached Files
    Last edited by etaf; 02-24-2014 at 07:34 PM.
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Help! If cell contains any sort of date, show green checkmark. Otherwise, show red..

    i thought maybe in column b

    =IF(AND(CELL("format",A1)="d1",A1>32874,A1<55153),1,-1) that will cover text and any date between
    01/01/1990
    31/12/2050
    then icon set rule is number
    >0 red
    >=0 yellow which it ignores
    and <0 red
    show icons only
    note it only detects the cell formatted as date in a certain format so if you are using a different one you need to change "d1" to match
    the formats are
    m/d/yy or m/d/yy h:mm or mm/dd/yy "D4"
    d-mmm-yy or dd-mmm-yy "D1"
    d-mmm or dd-mmm "D2"
    mmm-yy "D3"
    mm/dd "D5"
    Attached Files Attached Files
    Last edited by martindwilson; 02-24-2014 at 09:11 PM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Registered User
    Join Date
    02-23-2014
    Location
    Kentucky
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Help! If cell contains any sort of date, show green checkmark. Otherwise, show red..

    These examples helped! Thank you so much everyone. It fixed my problem!!

    I will close this thread as being solved now.

+ 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: 2
    Last Post: 12-10-2013, 02:30 AM
  2. Checkmark to show worksheet
    By menor59 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-09-2008, 05:52 PM
  3. pivot date sort/show
    By BorisS in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM
  4. [SOLVED] pivot date sort/show
    By BorisS in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-15-2005, 01:05 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