I have a spreadsheet that consist of dates when certifications expire. I am
trying to insert conditional formatting for two conditions. After much
headache I got one condition to work. However the second condition is giving
me a fit now.

This is the first condition which I got to work: I wanted to format the
cell with the date in it with a yellow fill color when I am between six
months and 1 month from expiration. I accomplished this with the following
formula for cell G1:

=AND(TODAY()G1-184, TODAY()<G1-31)

I was trying to use the EDATE(G1,-6) and EDATE(G1,-1) instead of the
(G1-184) and (G1-31) respectfully but could not get it to work.

The second condition that I am having trouble getting to work is to change
the fill color from yellow to red (with white lettering) when today's date is
within the expiration date or passed the expired date, indicated in cell G1.
I tried the formulas below which have not worked:

=TODAY() = G1-31
=TODAY() G1-32
=IF(TODAY()=EDATE(G1,-1))

The expiration date is in the same cell that the conditional format is in.
I am not in a separate cell referencing a different cell.

Any help is greatly appreciated!

Use

DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY())

(as an example of the 6 month, do the same for the 1 month)

you cannot use any add-in for conditional formatting and EDATE is part of
the ATP add-in

--
Regards,

Peo Sjoblom


"WLMPilot" wrote in message
...
I have a spreadsheet that consist of dates when certifications expire. I
am
trying to insert conditional formatting for two conditions. After much
headache I got one condition to work. However the second condition is
giving
me a fit now.

This is the first condition which I got to work: I wanted to format the
cell with the date in it with a yellow fill color when I am between six
months and 1 month from expiration. I accomplished this with the
following
formula for cell G1:

=AND(TODAY()G1-184, TODAY()<G1-31)

I was trying to use the EDATE(G1,-6) and EDATE(G1,-1) instead of the
(G1-184) and (G1-31) respectfully but could not get it to work.

The second condition that I am having trouble getting to work is to change
the fill color from yellow to red (with white lettering) when today's date
is
within the expiration date or passed the expired date, indicated in cell
G1.
I tried the formulas below which have not worked:

=TODAY() = G1-31
=TODAY() G1-32
=IF(TODAY()=EDATE(G1,-1))

The expiration date is in the same cell that the conditional format is in.
I am not in a separate cell referencing a different cell.

Any help is greatly appreciated!

Assuming the expiration date is in cell A3,

select 'Cell Value is' for 1st condition
First Condition:
less than or equal to

=today()


select 'Formula is' for 2d & 3d

Second condition:
=DATEDIF(TODAY(),A3,"m")<=1

Third condition
=DATEDIF(TODAY(),A3,"m")<=6

Format as you like

If the

"WLMPilot" wrote:

I have a spreadsheet that consist of dates when certifications expire. I am
trying to insert conditional formatting for two conditions. After much
headache I got one condition to work. However the second condition is giving
me a fit now.

This is the first condition which I got to work: I wanted to format the
cell with the date in it with a yellow fill color when I am between six
months and 1 month from expiration. I accomplished this with the following
formula for cell G1:

=AND(TODAY()G1-184, TODAY()<G1-31)

I was trying to use the EDATE(G1,-6) and EDATE(G1,-1) instead of the
(G1-184) and (G1-31) respectfully but could not get it to work.

The second condition that I am having trouble getting to work is to change
the fill color from yellow to red (with white lettering) when today's date is
within the expiration date or passed the expired date, indicated in cell G1.
I tried the formulas below which have not worked:

=TODAY() = G1-31
=TODAY() G1-32
=IF(TODAY()=EDATE(G1,-1))

The expiration date is in the same cell that the conditional format is in.
I am not in a separate cell referencing a different cell.

Any help is greatly appreciated!