Excel Conditional Formatting Help
I have been using the below formula to highlight all dates within the next 30 days.
=AND(E28-TODAY()>=0,E28-TODAY()<=30)
Although it's working fine, I want to tweak it.
In cell E28, I will now be entering a due date. I'm looking for a formula that will recognise that date and highlight all dates elsewhere on my page that are up to 30 days before that date.
I'm a bit out of my depth here, and when I tried =AND(E28-DATE(dd/mm/yyyy)<=0,E28-DATE(dd/mm/yyyy)>=30) it tells me I need more agruments.
Any help would be appreciated.
Your original CF construct looks good. Are the values in all the date cells regular Excel date values? If so, you shouldn't need to apply formatting to your formula.
If the range of cells to evaluate were A1:A100, then this would be the process:
1) Highlight A1:A100
2) Apply this CF formula:
=AND($E$28-A1>=0,$E$28-A1<=30)
The formula will adjust itself for all the cells applied.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Yes, they are all set to date values but there are eight columns containing dates relating to different things and I only want five of those columns to be included. (if that makes sense)Originally Posted by JBeaucaire
ie
A B C D E F G H I J
Columns C to J contain dates, I plan to enter a due date for invoice payment in E28 and I want any dates elsewhere in E G H I and J only, to be highlighted red if they are 30 days prior to the due date.
I basically want to swop the 'today' bit in the working formula for a fixed date if that is possible to do.
Just not sure if that effects the A1 part of the formula you've kindly put up.
Then use the technique I described above. You'll have to do it twice.
1) Highlight range E1:E100
=AND($E$28-E1>=0,$E$28-E1<=30)
2) Highlight range G1:J100
=AND($E$28-G1>=0,$E$28-G1<=30)
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Will do,
Thank you kindly
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks