+ Reply to Thread
Results 1 to 7 of 7

Highlighting Custom Date Function

  1. #1
    Registered User
    Join Date
    08-20-2019
    Location
    Richland, MS
    MS-Off Ver
    2019
    Posts
    4

    Exclamation Highlighting Custom Date Function

    I am creating a spreadsheet for work and am attempting to add a highlighting rule that applies a yellow highlight to a cell in a specific column (F) IF the date is more than 60 days ago from the current date (updating daily).
    I have created a new rule with

    =F1<(TODAY()-60)

    It is not telling me that the formula is wrong. It creates the rule no problem, but does not change any of the cells it should be changing.

    Can anyone help me see what I'm doing wrong???

    Thank you in advance.
    Last edited by samiam2013; 08-20-2019 at 04:08 PM.

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,726

    Re: Highlighting Custom Date Function

    Put the same formula in some column. See what is returned.

    If it returns all as FALSE, then likely issue is that dates stored in column F isn't actually date value (integer) but string value.

    Other than that, it would be difficult to help you without sample file. I'd recommend uploading sample workbook which is representative of your actual workbook with desensitized info to demonstrate your issue.

    To upload, use "Go Advanced" button and follow "Manage Attachments" hyperlink. It will launch new tab/window for managing uploads.
    Last edited by CK76; 08-20-2019 at 03:16 PM. Reason: F1 to column F
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  3. #3
    Forum Contributor
    Join Date
    03-28-2018
    Location
    Houston, TX
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    127

    Re: Highlighting Custom Date Function

    If the dates your are testing are in column F, then the formula is correct. It is possible that you have it applied to the incorrect range (should be F:F) or maybe you have no format set.

    Another thing that happens a lot when people attempt to create conditional formats is that more than one format gets created for a range of cells, and one format will overwrite another. Highlight column F and choose 'Manage Rules' from the 'Conditional Formatting' menu. Then make sure the 'Show formatting rules for:' box says 'Current Selection'. If there is more than one rule here, make sure to delete the rules that are old or incorrect and only leave the rule you want applied.

  4. #4
    Registered User
    Join Date
    08-20-2019
    Location
    Richland, MS
    MS-Off Ver
    2019
    Posts
    4

    Re: Highlighting Custom Date Function

    It all of a sudden took affect thankfully.
    And this won't let me upload the file for some reason.

    I have two rules set though, and now the other one isn't working. I know they are applied in the order they appear in the list so I have another rule above the date rule that states =G1="Terminated" and have the fill set to a different color regardless of the date. Now that one isn't cooperating with the second one.

    I am trying to figure out why the attach button on here won't let me attach it. May take a few screenshots and upload them.

  5. #5
    Registered User
    Join Date
    08-20-2019
    Location
    Richland, MS
    MS-Off Ver
    2019
    Posts
    4

    Re: Highlighting Custom Date Function

    Excel1.PNG
    Excel2.PNG
    Excel3.PNG

    Here are screenshots

  6. #6
    Forum Contributor
    Join Date
    03-28-2018
    Location
    Houston, TX
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    127

    Re: Highlighting Custom Date Function

    The other one isn't working because the 'applies to' range is incorrect. You have it set as $F$7. Should be F:F.

  7. #7
    Registered User
    Join Date
    08-20-2019
    Location
    Richland, MS
    MS-Off Ver
    2019
    Posts
    4

    Re: Highlighting Custom Date Function

    Oops, my mistake.
    Thank you all so much!
    It is solved. Not sure what was wrong with the first formula, it just randomly started working.

+ 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] Highlighting Closest Date
    By werevollf in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-01-2019, 05:17 AM
  2. macro for highlighting which will consider date
    By glaedr in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-09-2016, 07:57 PM
  3. [SOLVED] Highlighting Date
    By SeeMun in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-22-2016, 11:45 AM
  4. DAY() function is not working for custom date format
    By milindbharambe in forum Excel General
    Replies: 17
    Last Post: 04-24-2014, 07:19 AM
  5. Highlighting row based on the date
    By mamuka12 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-04-2013, 02:22 PM
  6. Highlighting a Row with the current Date
    By Hickup in forum Excel General
    Replies: 14
    Last Post: 03-07-2012, 11:17 AM
  7. Excel 2007 : Highlighting a Row with a Certain Date
    By Ormond2009 in forum Excel General
    Replies: 6
    Last Post: 03-29-2010, 03:02 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1