I am trying to find a formula for all cells that can take my battery dates that are 3 years old of the current date and or older that show highlighted red. I have used the formula =J10<TODAY()-1095 and it works for one cell but I cannot figure out how to us it for all cells. Please help
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
So the first sell I want to format is F1 format would look like =F1 < TODAY()-1095
Second cell would be F2 and the format would loo like =F2 < TODAY()-1095
The formula is taking any battery date 3 years and older from todays date and highlighting it red to show that it needs replaced. and eventually I would like to find out if I can get and automated email generated when that Cell turns red saying batteries needed updated at site etc.
Second cell would be F2 and the format would loo like =F2 < TODAY()-1095
The formula is taking any battery date 3 years and older from todays date and highlighting it red to show that it needs replaced. and eventually I would like to find out if I can get and automated email generated when that Cell turns red saying batteries needed updated at site etc.
Upvote
0
Excel Facts
Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Joe4
MrExcel MVP, Junior Admin
- Joined
- Aug 1, 2002
- Messages
- 74,140
- Office Version
- 365
- Platform
- Windows
OK, so if you wanted to apply this Conditional Formatting to all cells in the range A1:AC110, do the following:
- Select all the cells in your range A1:AC110 with your mouse
- Go to Conditional Formatting
- Enter the following Conditional Formatting formula:
- Choose your red formatting option
- Click OK to complete
- Select all the cells in your range A1:AC110 with your mouse
- Go to Conditional Formatting
- Enter the following Conditional Formatting formula:
Code:
[COLOR=#333333]=A1 < TODAY()-1095[/COLOR]- Click OK to complete
Upvote
0
Joe4
MrExcel MVP, Junior Admin
- Joined
- Aug 1, 2002
- Messages
- 74,140
- Office Version
- 365
- Platform
- Windows
Can you provide some sample dates that are not working for you?
Also, are you sure that those are actually date entries and not dates entered as text?
Here is one easy way to check. Let's say that the date in question is in cell F1.
Then in any empty cell, enter this formula and see what it returns:
=ISNUMBER(F1)
If it returns FALSE, you do not have a valid date entry in that cell.
Also, are you sure that those are actually date entries and not dates entered as text?
Here is one easy way to check. Let's say that the date in question is in cell F1.
Then in any empty cell, enter this formula and see what it returns:
=ISNUMBER(F1)
If it returns FALSE, you do not have a valid date entry in that cell.
Upvote
0
That is interesting. The cells that are not comin gup when I tried a few to see if it would work are J10 with date 6/6/2013 and F10 with date 6/8/15. The 6/8/2013 date is correct to be highlighted but then lets say I just updeated it to 6/6/2018 it still shows red when It shouldn't. so I typed what I said in a blank cell and it does come back false but there is no date entry in there so it should come back false right?
Upvote
0
