Log In
Register
Home
June 21, 2020
Offline
I am trying to create conditional formatting to highlight date cells in a column that are in the future. I am currently trying this formula:
=AND($E15>TODAY(),$E15>=(TODAY()>30))
I have conditions using the standard drop downs for last month, this month and next month, but I want to highlight all dates after next month also. I have used >30 in the formula above to try and highlight everything longer than 30 days but it doesn't seem to work.
Would appreciate any guidance
See Answer
Trusted Members
December 20, 2019
Offline
Seems to work for me, where are you putting your formula in?
Is always better to add an excample worksheet so we can see what is happening and we then dont have a to recreate the data
June 21, 2020
Offline
Thanks for your reply.
I have hopefully attached a sample file here :
As you will see, I have an orange highlight for dates next month but also trying to create the same for dates in the future after and beyond next month. You can see that dates for November are not being highlighted.
Hope I am explaining this clearly.
VIP
Trusted Members
December 7, 2016
Offline
Hello,
If you change the formula to =AND($E15>TODAY(),$E15>=(TODAY()+30)) it works as you want.
Br,
Anders
June 21, 2020
Offline
Anders,
Thanks for that. I did try using the + but for some strange reason, it didn't work. It is working now though so thank you for that.
I have now noticed another issue now where I want to highlight past dates, the standard drop-down only has dates for last month which means those dates further back than a month don't get highlighted and remain blank.
Again I have tried tweaking the formula to get this result but seems to highlight even the blank cells. Clearly I am doing something wrong.
VIP
Trusted Members
December 7, 2016
Offline
Hello,
Use this formula to highlight dates two months or more back.
=AND(NOT(ISBLANK($E15)),$E15<DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(1)))
Br,
Anders
Answers Post
1 Guest(s)
