+ Reply to Thread
Results 1 to 10 of 10

Conditional Formatting Question

  1. 11-17-2016, 11:28 AM #1
    lbickford
    lbickford is offline
    Registered User
    Join Date
    11-25-2015
    Location
    Minneapolis, MN
    MS-Off Ver
    2013
    Posts
    47

    Conditional Formatting Question

    I am losing my mind. I frequently use conditional formatting, but for the life of me, I can't figure out why it is completely unresponsive in this workbook. I use it all the time to highlight dates that are upcoming (highlight between today and today+30), things that are overdue, etc. In the attached workbook, I am trying to highlight column F with Highlight Cells Between =today() and =today+30. I can see with my own eyeballs that there are dates between now and December 17th, and they are stubbornly not highlighting!!! WHY?! But when I select "Highlight cells equal to" and pick a random cell, it works, so I know it's not like I'm working in a formatless void of a workbook - it CAN do it, it just....won't!!!

    What I really need is someone to explain to me what is happening, why it's not working - I am less interested in someone delivering what I want. I can do it myself because I do it all the time - I just want to know why it's not working now!

    Also, hopefully the attachment shows up. This UI from 1995 isn't exactly making me feel confident.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 11-17-2016, 11:38 AM #2
    Special-K
    Special-K is offline
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,064

    Re: Conditional Formatting Question

    All the dates in column F are text, hence the left justification

    And there's no Conditional Formatting in this spreadsheet for column F at all.
    Last edited by Special-K; 11-17-2016 at 11:40 AM.
    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.
    Reply With QuoteRegister To Reply
  3. 11-17-2016, 11:40 AM #3
    lbickford
    lbickford is offline
    Registered User
    Join Date
    11-25-2015
    Location
    Minneapolis, MN
    MS-Off Ver
    2013
    Posts
    47

    Re: Conditional Formatting Question

    They are showing for me as "Date" and I have selected "Short Date" several times - I thought this might be an issue too, but I don't think that's it....??!
    Reply With QuoteRegister To Reply
  4. 11-17-2016, 11:41 AM #4
    Special-K
    Special-K is offline
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,064

    Re: Conditional Formatting Question

    in a blank cell enter ISTEXT(F1)
    It says TRUE
    They are text.

    in a blank cell enter
    =F1+1 which should be the next day.
    It says #VALUE.
    They are definitely text.

    If I enter '29/10/1961 into a cell it left justifies it as text which is correct.
    If I then format the cell as date, it returns date as the format
    but it is still text. ISTEXT() returns TRUE.

    Are you sure youve uploaded the correct spreadsheet?
    Last edited by Special-K; 11-17-2016 at 11:45 AM.
    Reply With QuoteRegister To Reply
  5. 11-17-2016, 11:41 AM #5
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,939

    Re: Conditional Formatting Question

    Your dates are text looking like dates. Correct this with Text2Columns
    1. select teh range
    2. click data tab/T2C
    3. click next/next/select Date and chose your format
    4. click OK
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  6. 11-17-2016, 11:42 AM #6
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,939

    Re: Conditional Formatting Question

    Quote Originally Posted by lbickford View Post
    They are showing for me as "Date" and I have selected "Short Date" several times - I thought this might be an issue too, but I don't think that's it....??!
    If they are text dates, no amount of formatting will change that. For instance, try using Long Date format - they wont change
    Reply With QuoteRegister To Reply
  7. 11-17-2016, 11:45 AM #7
    lbickford
    lbickford is offline
    Registered User
    Join Date
    11-25-2015
    Location
    Minneapolis, MN
    MS-Off Ver
    2013
    Posts
    47

    Re: Conditional Formatting Question

    ...it worked. WAT. But can you explain why on earth it was still registering the format of Column F as "Text" even though I had selected "Date"???? Why would it do that to me?
    Reply With QuoteRegister To Reply
  8. 11-17-2016, 05:01 PM #8
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,939

    Re: Conditional Formatting Question

    Where is the data coming from?
    Probably imported from another program? If so, then data from external programs often gets imported as text, and values (and dates are values), may look like numbers, but excel sees them as text

    Thanks for the re, too
    Reply With QuoteRegister To Reply
  9. 11-17-2016, 05:07 PM #9
    lbickford
    lbickford is offline
    Registered User
    Join Date
    11-25-2015
    Location
    Minneapolis, MN
    MS-Off Ver
    2013
    Posts
    47

    Re: Conditional Formatting Question

    It did indeed come from a program export. Thanks for all of your help! Glad you got it figured out for me - I thought I was going insane!
    Reply With QuoteRegister To Reply
  10. 11-17-2016, 05:09 PM #10
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,939

    Re: Conditional Formatting Question

    No problem, come back again soon
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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 Formatting question.
    By Losguapos1 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-18-2015, 01:08 PM
  2. Conditional formatting question
    By pmb1029 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-23-2009, 06:35 AM
  3. VB Conditional Formatting Question??
    By Jay3 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-21-2009, 12:05 PM
  4. Conditional Formatting Question
    By daredevil in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-16-2007, 10:42 AM
  5. conditional formatting question
    By rwethington in forum Excel General
    Replies: 3
    Last Post: 09-22-2006, 03:22 PM
  6. [SOLVED] Conditional formatting question
    By Carl Imthurn in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 PM
  7. Another Conditional Formatting Question
    By MEL79 in forum Excel General
    Replies: 5
    Last Post: 03-12-2005, 06:14 PM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1