+ Reply to Thread
Results 1 to 6 of 6

Conditional formating dates

  1. #1
    Forum Contributor
    Join Date
    06-21-2013
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2010
    Posts
    186

    Conditional formating dates

    gurus,


    I am trying to conditionally format a cell that when a date is entered, the cell is filled green. However, if the cell does not contain a date it should be no fill. Meaning, if the user enters a hyphen or NA in the cell, I don't want the cell to format to green.

    Unfortunately in my sample spreadsheet, when a hyphen or even NA is entered in the cell, the cell fill is set to green. I cannot figure out the formula that only sets the fill to green when a date is entered.

    I understand that excel translates dates to numbers, but I don't understand why a hyphen or even NA is also triggering the fill setting.

    I have tried a couple of formulas in my sample to no avail. Please help and show my what I am doing wrong.

    TIA,
    Tim
    Attached Files Attached Files

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Conditional formating dates

    Use this formula for conditional formatting

    select column where you want to apply conditional formatting, enter formula and select fill color.

    if your data in Column G...

    =ISNUMBER(G5)

    otherwise change the cell reference.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Forum Contributor
    Join Date
    06-21-2013
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2010
    Posts
    186

    Re: Conditional formating dates

    AlKey,

    I followed your instructions but it didn't seem to work.

    Tim

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Conditional formating dates

    Quote Originally Posted by TimlmiT View Post
    AlKey,

    I followed your instructions but it didn't seem to work.

    Tim
    Pleas see attached file. Go to CF and see who it was set up.
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    06-21-2013
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2010
    Posts
    186

    Re: Conditional formating dates

    Alkey,

    I found that the statement "select the column" was incorrect. I had to select the cells within the column, not the whole column. That was where the confusion was.

    Anyway, it works now. I had added a few other conditions in order for it to work the way I wanted it to.

    Thanks for pointing me in the right direction!

    Tim
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    06-21-2013
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2010
    Posts
    186

    Re: Conditional formating dates

    All,

    Please use this example.

    Tim
    Attached Files Attached Files

+ 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. [SOLVED] Conditional Formating of Dates
    By Karen615 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-11-2014, 01:42 PM
  2. Conditional Formating Dates
    By LKH2859 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-16-2013, 10:32 AM
  3. I need help with a conditional formating using dates
    By Gumbercules563 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-21-2013, 09:59 AM
  4. Excel 2007 : Conditional Formating using dates
    By kuntz92 in forum Excel General
    Replies: 2
    Last Post: 11-02-2011, 08:31 AM
  5. Install dates formating using conditional formating?
    By Jerry Eggleston in forum Excel General
    Replies: 2
    Last Post: 11-09-2005, 01:49 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