+ Reply to Thread
Results 1 to 4 of 4

Conditional Formatting & Date Cell with Text

  1. #1
    Registered User
    Join Date
    06-08-2015
    Location
    Riyadh
    MS-Off Ver
    Microsoft Office Standard 2010
    Posts
    2

    Conditional Formatting & Date Cell with Text

    Hello everyone!

    I have a slight problem with Conditional Formatting and how it deals with text that comes after (or before) a certain date in the same cell. I have added rules that would color each Date cell by how far it is from today. I have used "Cell Value" [between =Today() & =Today() + 30 for example]. However, when I add the letter "A" in the same cell, it completely changes the value of the cell [for example: if a date is supposed be 15 days away, it should be red colored. But when I add the letter "A", it becomes blue (red = up to 30 days away, Blue = date is more than a year away)].

    Excel Sample.jpg

    I remember doing something that would make the cell have the correct color while still displaying the letter next to the date.
    The reason I add the letter "A" is because I want to differentiate between actual & estimated dates.

    Thank you for considering this.

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Conditional Formatting & Date Cell with Text

    Cells with a valid date contain numbers.
    As soon as you add the letter A in the cell it is regarded as text.

    Why dont you just inserta column between the dates and add the letter A there?
    That way it wont affect the conditional formatting
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Registered User
    Join Date
    06-08-2015
    Location
    Riyadh
    MS-Off Ver
    Microsoft Office Standard 2010
    Posts
    2

    Re: Conditional Formatting & Date Cell with Text

    Quote Originally Posted by Special-K View Post
    Cells with a valid date contain numbers.
    As soon as you add the letter A in the cell it is regarded as text.

    Why dont you just inserta column between the dates and add the letter A there?
    That way it wont affect the conditional formatting
    I know I could add a column. The problem is that it doesn't look presentable.

  4. #4
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Conditional Formatting & Date Cell with Text

    Try

    =(TODAY()-LEFT(A1,8)+0)>15

    format as red
    Base others on these.

+ 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. conditional formatting to a cell (date and text)
    By curtisw in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-14-2015, 11:32 PM
  2. Replies: 1
    Last Post: 01-30-2014, 10:04 AM
  3. Replies: 1
    Last Post: 06-02-2013, 04:00 PM
  4. [SOLVED] Conditional formatting based on text in one cell, date in another
    By Chuck_N in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-29-2013, 11:34 AM
  5. Conditional formatting based on text in cell and a corresponding date
    By livinglegend629 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-11-2010, 05:10 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