Conditional formatting on specifica date ranges

[SelinaR]

Hi there

I would like to put in cond. formatting on date ranges that will pick up from TODAY() - I have tried various options - pre set on excel and my own formula
of greater than, or equal to such as =TODAY() & =NOW() & DATEDIF, etc, but seem to be getting blank zones within the range and my formula's specific to today vs. 28 days from now . Please help - I am looking for more of a range that will format rather than an actual date to highlight

Example of rules (all feeding off of today &/or an input date):

1st	Within 28 Days (current)
2nd	After 28 days (going to lapse)
3rd	>29 days & up to 3 months
4th	over 3 Months
5th	>6 Months

<colgroup><col><col></colgroup><tbody>
</tbody>

Thanks so much for your help in advance.
Selina

 

[smallxyz]

Is that you have a sheet of calendar, and subject to your input date, you would like Excel to CF those calendar dates according to your specified criteria?

 

[SpreedsheetCrusader]

rule 1: highlight with yellow

=AND((D$3-TODAY())<=28,(D$3-TODAY())>=0)

rule 2 : - i did not understand it

rule 3: highlight with purple

=AND((D$3-TODAY())>29,(D$3-TODAY())<90)

rule 4: highlight with green

=(D$3-TODAY())>=90

rule 5: highlight with blue

=(XFA$3-TODAY())>180

all your conditional formatting rules should be referenced on the desired range.

use a relative reference in the formula,, no $ sign on column letter.

 

[SelinaR]

Hiya thanks for that - its more like this:

scenario - have to contact a member within certain dates of a return to sender mail (not network days - total days)

1st	Within 28 Days (current) - green
2nd	After 28 days (Up to maximum of 28 days from today's date) - orange
3rd	over 29 days & up to 90 days - red
4th	=>91 days to over 180 days - bright red.
5th	=>181 days - maroon

<tbody>
</tbody>

Col A1
TODAY'S DATE
=TODAY() i.e 30/11

Col A Col B Col C
RTS DATE Priority Callback
01/12 1 Immediate
25/12 2 Yes
20/10 3 Attempt contact
20/07 4 Note and archive

*the conditional formatting is reading from: todays' date then the RTS date will have the priority colours

The conditional format - highlight rules - date occuring is great, but is only locked into this week, next week etc... blocks of time that are outside of my criteria.

Thanks

 

[SelinaR]

*Date on RTA Mail	Priority	2nd Method of Contact (use drop down/type)
28/12/2015		Mobile
25/12/2015		Home no.
1/12/2015		Email
30/11/2015		Mobile
20/11/2015		Home no.
20/10/2015		Email
5/09/2015		All
1/12/2015

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[smallxyz]

Excel 2007
	A	B	C	D	E	F
1	RTS DATE	Priority	Callback	1st rule	=(($A2-TODAY())<=27)	green
2	2015/12/1	1	Immediate	2nd rule	=(($A2-TODAY())=28)	orange
3	2015/12/25	2	Yes	3rd rule	=AND(($A2-TODAY())>=29,($A2-TODAY())<90)	red
4	2016/10/20	3	Attempt contact	4th rule	=AND(($A2-TODAY())>=91,($A2-TODAY())<180)	bright red
5	2016/7/20	4	Note and archive	5th rule	=(($A2-TODAY())>=181)	maroon
Sheet2

 

[SelinaR]

Hiya

Thanks for the reply... my example was for the colour coding within ranges, so could you let me know which parameter your using in conditional Formatting:

CELL VALUE -- EQUAL TO -- and then putting in the formula or
FORMULA VALUES ?
*I've tried both options and it doesn't seem to be working for me - have I missed something?

Your formula looks sound, but is only coming off of a cell reference.... I require it to be against a range and attempted to input that, but not working either?

 

[SelinaR]

I have Excel 2010