I want to format cell A1 to turn red if the date in cell A1 is not within the
current or previous quarter date specified in cell B2 with:
=DATE(YEAR(TODAY()),INDEX({3;6;9;12},INT((MONTH(TODAY())+2)/3))+1,0)

try this

=NOT(OR(MONTH(TODAY())-MONTH(A2)<=3,MONTH(TODAY())-MONTH(A2)<=6))

On Dec 2, 6:51*am, Loadmaster <Loadmas...@discussions.microsoft.com>
wrote:
> I want to format cell A1 to turn red if the date in cell A1 is not withinthe
> current or previous quarter date specified in cell B2 with:
> =DATE(YEAR(TODAY()),INDEX({3;6;9;12},INT((MONTH(TODAY())+2)/3))+1,0)

Hi,

You can simplify your formula for the last date of a quarter to

=EOMONTH(NOW(),MOD(3-MONTH(NOW()),3))

The EOMONTH function is part of the Analysis Toolpak so you need to make
sure that is attached in 2003 or earlier - choose Tools, Add-ins, and check
it.

To conditionally format your cell(s):

In 2003:
1. Select the cell you want to format
2. Choose Format, Conditional Formatting
3. Choose Formula is from the first drop down
4. In the second box enter the formula:
=A1<=DATE(YEAR(A2-183.5),MONTH(A2-183.5)+1,1)-1
5. Click the Format button
6. Choose a color on the Patterns tab (or any available option)
7. Click OK twice.

In 2007:
1. Highlight all the cells on the rows you want formatted
2. Choose Home, Conditional Formatting, New Rule
3. Choose Use a formula to determine which cell to format
4. In the Format values where this formula is true enter the following
formula:
=A1<=EDATE(A2,-6)
5. Click the Format button and choose a format.
6. Click OK twice

If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Loadmaster" wrote:

> I want to format cell A1 to turn red if the date in cell A1 is not within the
> current or previous quarter date specified in cell B2 with:
> =DATE(YEAR(TODAY()),INDEX({3;6;9;12},INT((MONTH(TODAY())+2)/3))+1,0)

Yes thank you it worked, but now instead what I want is a conditional format
that if on the last day of the end of the quarter there is no date within
that quarter for the cell to turn red and remain red into the next quarter
until a new date within the next quarter is entered in A1.

"Shane Devenshire" wrote:

> Hi,
>
> You can simplify your formula for the last date of a quarter to
>
> =EOMONTH(NOW(),MOD(3-MONTH(NOW()),3))
>
> The EOMONTH function is part of the Analysis Toolpak so you need to make
> sure that is attached in 2003 or earlier - choose Tools, Add-ins, and check
> it.
>
> To conditionally format your cell(s):
>
> In 2003:
> 1. Select the cell you want to format
> 2. Choose Format, Conditional Formatting
> 3. Choose Formula is from the first drop down
> 4. In the second box enter the formula:
> =A1<=DATE(YEAR(A2-183.5),MONTH(A2-183.5)+1,1)-1
> 5. Click the Format button
> 6. Choose a color on the Patterns tab (or any available option)
> 7. Click OK twice.
>
> In 2007:
> 1. Highlight all the cells on the rows you want formatted
> 2. Choose Home, Conditional Formatting, New Rule
> 3. Choose Use a formula to determine which cell to format
> 4. In the Format values where this formula is true enter the following
> formula:
> =A1<=EDATE(A2,-6)
> 5. Click the Format button and choose a format.
> 6. Click OK twice
>
> If this helps, please click the Yes button.
>
> Cheers,
> Shane Devenshire
>
>
> "Loadmaster" wrote:
>
> > I want to format cell A1 to turn red if the date in cell A1 is not within the
> > current or previous quarter date specified in cell B2 with:
> > =DATE(YEAR(TODAY()),INDEX({3;6;9;12},INT((MONTH(TODAY())+2)/3))+1,0)