Dates for Today and Today +7 conditional formatting

nutshellml

New Member
Joined
Oct 15, 2017
Messages
6
You guys helped me out last time and now at another sticking point. I'm looking to highlight excel columns for appointments that happen today and anytime in the next 7 days, but having some trouble.

I tried: =$A2>=TODAY()+7 and it formatted all of the dates 7 days from today forward, leaving the dates that are within the (week) 7 days blank.

So I thought, let me just invert the formula: =$A2<=TODAY()-7 and nothing was formatted.

I know I'm missing something in the formula to show I want it GREATER than TODAY AND less than 7 DAYS from TODAY
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
if you want to test for "within 7 days from today"...
=and($A2>=today(),$A2<=today()+7)
 
Upvote 0
GOT IT AGAIN: =AND($A2<=TODAY()+7,$A2>=TODAY())

I don't understand why the $A2<=TODAY()+7 needs to go before $A2>=TODAY(). If i reverse it, the formula won't work... go figure..
 
Upvote 0
I just tested the formula again, and it didn't matter which way round I put the criteria, it worked OK both ways
A​
B​
C​
2​
10/22/2017​
TRUE​
AND($A2>=TODAY(),$A2<=TODAY()+7)
3​
10/22/2017​
TRUE​
AND($A3<=TODAY()+7,A3>=TODAY())
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top