If cell = now €“ current Qtr €“ 28 days then RED
 

Im trying to work out a formula which is based upon quarters. So, if the
date in column C is more than 28 days + 3 months (i.e. last quarter) + this
quarter then the cell displays RED

The quarters run
Qtr 1 01/01 €“ 31/03
Qtr 2 01/04 €“ 30/06
Qtr 3 01/07 €“ 31/09
Qtr 4 01/10 €“ 31/12

So essentially, working on the quarter were in (Currently Qtr 3) if a date
is displayed that is older than 28 days prior to the beginning of Qtr 2 I
want it highlighting. But I need it to be consistent so I can repeat it at
any time in the year.

Any help would be very much appreciated.

Regards,

Terry

If cell = now €“ current Qtr €“ 28 days then RED
 

1. Select Column C
2. From menu FormatConditional Formatting
3. For Condition1Select 'Formula Is' and enter the below formula
=AND(C1<"",C1<DATE(YEAR(TODAY()),CEILING(MONTH(TO DAY()),3)-5,1)-28)

4. Click Format ButtonPattern and select your color (say Red)
5. Hit OK


If this post helps click Yes
---------------
Jacob Skaria


"Tel" wrote:

Im trying to work out a formula which is based upon quarters. So, if the
date in column C is more than 28 days + 3 months (i.e. last quarter) + this
quarter then the cell displays RED

The quarters run
Qtr 1 01/01 €“ 31/03
Qtr 2 01/04 €“ 30/06
Qtr 3 01/07 €“ 31/09
Qtr 4 01/10 €“ 31/12

So essentially, working on the quarter were in (Currently Qtr 3) if a date
is displayed that is older than 28 days prior to the beginning of Qtr 2 I
want it highlighting. But I need it to be consistent so I can repeat it at
any time in the year.

Any help would be very much appreciated.

Regards,

Terry

If cell = now €“ current Qtr €“ 28 days then RED
 

Jacob,

Not only does it work I happen to think you're an absolute bloody star. I
could kiss youi, but if it's all the same I won't :-)

Thank you so much

Terry

"Jacob Skaria" wrote:

1. Select Column C
2. From menu FormatConditional Formatting
3. For Condition1Select 'Formula Is' and enter the below formula
=AND(C1<"",C1<DATE(YEAR(TODAY()),CEILING(MONTH(TO DAY()),3)-5,1)-28)

4. Click Format ButtonPattern and select your color (say Red)
5. Hit OK


If this post helps click Yes
---------------
Jacob Skaria


"Tel" wrote:

Im trying to work out a formula which is based upon quarters. So, if the
date in column C is more than 28 days + 3 months (i.e. last quarter) + this
quarter then the cell displays RED

The quarters run
Qtr 1 01/01 €“ 31/03
Qtr 2 01/04 €“ 30/06
Qtr 3 01/07 €“ 31/09
Qtr 4 01/10 €“ 31/12

So essentially, working on the quarter were in (Currently Qtr 3) if a date
is displayed that is older than 28 days prior to the beginning of Qtr 2 I
want it highlighting. But I need it to be consistent so I can repeat it at
any time in the year.

Any help would be very much appreciated.

Regards,

Terry

If cell = now €“ current Qtr €“ 28 days then RED
 

Terry, thanks for your feedback..Cheers

If this post helps click Yes
---------------
Jacob Skaria


"Tel" wrote:

Jacob,

Not only does it work I happen to think you're an absolute bloody star. I
could kiss youi, but if it's all the same I won't :-)

Thank you so much

Terry

"Jacob Skaria" wrote:

1. Select Column C
2. From menu FormatConditional Formatting
3. For Condition1Select 'Formula Is' and enter the below formula
=AND(C1<"",C1<DATE(YEAR(TODAY()),CEILING(MONTH(TO DAY()),3)-5,1)-28)

4. Click Format ButtonPattern and select your color (say Red)
5. Hit OK


If this post helps click Yes
---------------
Jacob Skaria


"Tel" wrote:

Im trying to work out a formula which is based upon quarters. So, if the
date in column C is more than 28 days + 3 months (i.e. last quarter) + this
quarter then the cell displays RED

The quarters run
Qtr 1 01/01 €“ 31/03
Qtr 2 01/04 €“ 30/06
Qtr 3 01/07 €“ 31/09
Qtr 4 01/10 €“ 31/12

So essentially, working on the quarter were in (Currently Qtr 3) if a date
is displayed that is older than 28 days prior to the beginning of Qtr 2 I
want it highlighting. But I need it to be consistent so I can repeat it at
any time in the year.

Any help would be very much appreciated.

Regards,

Terry