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.
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
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.
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.
Excel1.PNG
Excel2.PNG
Excel3.PNG
Here are screenshots
The other one isn't working because the 'applies to' range is incorrect. You have it set as $F$7. Should be F:F.
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks