What is formula to have an upcoming date change color?
 

I have a spreadsheet that shows contract begin and end dates. I would like
the end date to highlight or change colors when it is two months away.

What is formula to have an upcoming date change color?
 

Hey, try this... it should work by highlighting the cells that are within two
months away. Select the column that has the dates.... then go to

Format--- Conditional Formatting

Then change Condition 1 to---- Formula is

And in the Formula Box type

=AND(A1-TODAY()<60,A1-TODAY()0)

REMEMBER**** you have to Change A1 to whereever your first date starts. So
if your dates are in column H... you will change the A1s to H1s, etc.

"Sunset" wrote:

I have a spreadsheet that shows contract begin and end dates. I would like
the end date to highlight or change colors when it is two months away.

What is formula to have an upcoming date change color?
 

Select the cell you want to change, assume it is A2, do format conditional
formatting, select formula is and use

=DATE(YEAR(TODAY()),MONTH(TODAY())+2,DAY(TODAY())) =A2

click the format button and select the format you want

what do you want to happen when the date is less than today? If you want to
remove the formatting you can use

=AND(A2=TODAY(),DATE(YEAR(TODAY()),MONTH(TODAY()) +2,DAY(TODAY()))=A2)


--
Regards,

Peo Sjoblom



"Sunset" wrote in message
...
I have a spreadsheet that shows contract begin and end dates. I would like
the end date to highlight or change colors when it is two months away.

What is formula to have an upcoming date change color?
 

Seems to be the right direction, although the boxes that highlighted either
have no information or are dated 12-30-2007. The column is the "H" beginning
with row "7"

"AKphidelt" wrote:

Hey, try this... it should work by highlighting the cells that are within two
months away. Select the column that has the dates.... then go to

Format--- Conditional Formatting

Then change Condition 1 to---- Formula is

And in the Formula Box type

=AND(A1-TODAY()<60,A1-TODAY()0)

REMEMBER**** you have to Change A1 to whereever your first date starts. So
if your dates are in column H... you will change the A1s to H1s, etc.

"Sunset" wrote:

I have a spreadsheet that shows contract begin and end dates. I would like
the end date to highlight or change colors when it is two months away.

What is formula to have an upcoming date change color?
 

Try just doing it for the entire column starting with H1, because it works
perfectly on my spreadsheet. It only highlights values that are today through
2 months... and leaves all text and other dates alone.

"Sunset" wrote:

Seems to be the right direction, although the boxes that highlighted either
have no information or are dated 12-30-2007. The column is the "H" beginning
with row "7"

"AKphidelt" wrote:

Hey, try this... it should work by highlighting the cells that are within two
months away. Select the column that has the dates.... then go to

Format--- Conditional Formatting

Then change Condition 1 to---- Formula is

And in the Formula Box type

=AND(A1-TODAY()<60,A1-TODAY()0)

REMEMBER**** you have to Change A1 to whereever your first date starts. So
if your dates are in column H... you will change the A1s to H1s, etc.

"Sunset" wrote:

I have a spreadsheet that shows contract begin and end dates. I would like
the end date to highlight or change colors when it is two months away.

What is formula to have an upcoming date change color?
 

That didn't do it either, my cells with 5/31/2007 should have highlighted, I
only had one dated 6/30/2007 that highlighted out of 4, and still many with
N/As that highlighted.

"Peo Sjoblom" wrote:

Select the cell you want to change, assume it is A2, do format conditional
formatting, select formula is and use

=DATE(YEAR(TODAY()),MONTH(TODAY())+2,DAY(TODAY())) =A2

click the format button and select the format you want

what do you want to happen when the date is less than today? If you want to
remove the formatting you can use

=AND(A2=TODAY(),DATE(YEAR(TODAY()),MONTH(TODAY()) +2,DAY(TODAY()))=A2)


--
Regards,

Peo Sjoblom



"Sunset" wrote in message
...
I have a spreadsheet that shows contract begin and end dates. I would like
the end date to highlight or change colors when it is two months away.

What is formula to have an upcoming date change color?
 

Except that it won't work for all months since you assume that 2 months are
60 days whereas Jan and Feb are 59 or 60 or Jul and Aug are 62 meaning that
when today is for instance either 28, 29 or 30th December and the future
date is 02/28 it won't change the format.


--
Regards,

Peo Sjoblom



"AKphidelt" wrote in message
...
Try just doing it for the entire column starting with H1, because it works
perfectly on my spreadsheet. It only highlights values that are today
through
2 months... and leaves all text and other dates alone.

"Sunset" wrote:

Seems to be the right direction, although the boxes that highlighted
either
have no information or are dated 12-30-2007. The column is the "H"
beginning
with row "7"

"AKphidelt" wrote:

Hey, try this... it should work by highlighting the cells that are
within two
months away. Select the column that has the dates.... then go to

Format--- Conditional Formatting

Then change Condition 1 to---- Formula is

And in the Formula Box type

=AND(A1-TODAY()<60,A1-TODAY()0)

REMEMBER**** you have to Change A1 to whereever your first date starts.
So
if your dates are in column H... you will change the A1s to H1s, etc.

"Sunset" wrote:

I have a spreadsheet that shows contract begin and end dates. I would
like
the end date to highlight or change colors when it is two months
away.

What is formula to have an upcoming date change color?
 

Either your dates are text or you fail to implement the formatting correctly


--
Regards,

Peo Sjoblom



"Sunset" wrote in message
...
That didn't do it either, my cells with 5/31/2007 should have highlighted,
I
only had one dated 6/30/2007 that highlighted out of 4, and still many
with
N/As that highlighted.

"Peo Sjoblom" wrote:

Select the cell you want to change, assume it is A2, do format
conditional
formatting, select formula is and use

=DATE(YEAR(TODAY()),MONTH(TODAY())+2,DAY(TODAY())) =A2

click the format button and select the format you want

what do you want to happen when the date is less than today? If you want
to
remove the formatting you can use

=AND(A2=TODAY(),DATE(YEAR(TODAY()),MONTH(TODAY()) +2,DAY(TODAY()))=A2)


--
Regards,

Peo Sjoblom



"Sunset" wrote in message
...
I have a spreadsheet that shows contract begin and end dates. I would
like
the end date to highlight or change colors when it is two months away.

What is formula to have an upcoming date change color?
 

Thank you for your help

"AKphidelt" wrote:

Try just doing it for the entire column starting with H1, because it works
perfectly on my spreadsheet. It only highlights values that are today through
2 months... and leaves all text and other dates alone.

"Sunset" wrote:

Seems to be the right direction, although the boxes that highlighted either
have no information or are dated 12-30-2007. The column is the "H" beginning
with row "7"

"AKphidelt" wrote:

Hey, try this... it should work by highlighting the cells that are within two
months away. Select the column that has the dates.... then go to

Format--- Conditional Formatting

Then change Condition 1 to---- Formula is

And in the Formula Box type

=AND(A1-TODAY()<60,A1-TODAY()0)

REMEMBER**** you have to Change A1 to whereever your first date starts. So
if your dates are in column H... you will change the A1s to H1s, etc.

"Sunset" wrote:

I have a spreadsheet that shows contract begin and end dates. I would like
the end date to highlight or change colors when it is two months away.