Here is my formula for highlighting the cells during the 30 days leading up to the expiration date:
=AND(TODAY()>=A1+335, TODAY()<A1+365)
I would like to modify this formula to highlight dates that are current before the 30 days in green and dates that are overdue in red.
Thank you for the help.
You need to use Conditional Formatting for that. If you are unfamiliar with using that, let me know.
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
Thanks for replying. I'm familiar with conditional formatting. I just needed help in modifying the formula for reflect current dates in green and overdue dates in red.
Dear FDibbins
try this
Please Login or Register to view this content.
If I understand your problems with green and red dates I would do like this
Produkt date older than 30 days from today highligt in red
Produkt date less than 30 days from today highligt in green
Use Conditional Formatting, Use a formula, and format fill
For example if You have Produkt 1 date in cell A1
Put 2 separate formulas in cell A1
Green formula =A1>TODAY()-30 Format Fill green
Red formula =A1<TODAY()-30 Format Fill Red
Copy cell A1 and Paste Special - Formats to all other Produkt dates
All produkt older than 30 days will hightlight red and less 30 days in green
/LGS
Last edited by LARS GULYAS; 01-03-2024 at 10:03 AM.
New formulas:
Use Conditional Formatting,
"Use a formula to determine which cells to format"
For example if You have Product 1 date in cell A1
All products older than 30 days will hightlight red and less 30 days in green,Today in yellow.
Put 3 separate formulas in cell A1
Today formula =INT(A1)=TODAY()
Green formula =AND(A1>TODAY()-30,ISNUMBER(A1))
Red formula =AND(A1<TODAY()-30,ISNUMBER(A1))
Date is actually a number so adding ISNUMBER filters out text and empty cells
Copy cell A1 and Paste Special - Formats to all other Product dates
/LGS
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks