Date Query

**Eric60** · 07-01-2009, 04:38 AM

Hi All

I am pretty new to excel, using MS2004.

I am entering dates in a column, that tell me when documents have been sent to clients.

Is there anyway of setting a formula that changes the colour of the cell 14 days after the entered date? (So we can see what documents are due back?)

Many thanks
Carl

**DonkeyOte** · 07-01-2009, 05:32 AM

have a read through the Contextures website chapter on Conditional Formatting - in the third section there are examples of a variety of tests, one of which being date related.

http://www.contextures.com/xlCondFormat01.html

**Eric60** · 07-01-2009, 06:23 AM

Many thanks for your quick response -

One last query -

I am dealing with varied dates -
After choosing the 'greater than'

What do i type in the text box if i want a 14 day warning?
'14 days'

**highbarger** · 07-01-2009, 06:35 AM

If the date that the document was sent is in cell D2 for example, put a formula in E2 that is something like =TODAY()-D2. Make sure that this column is not formatted as a date. Then use the conditional formatting set at greater than 14.

**DonkeyOte** · 07-01-2009, 06:47 AM

You might want to investigate the FormulaIs based approach...

http://www.contextures.com/xlCondFormat03.html#Expiry

**highbarger** · 07-01-2009, 08:51 AM

I was hurrying off to work when I replied earlier... it might work easier for you to set the conditional formatting option to Formula Is: =TODAY()-D2>14 where D2 is the date you're comparing. Set the format to Red. Then use the format painter to change any other cells to the same conditional format. This approach doesn't require any extra columns to be used.

**Eric60** · 07-01-2009, 10:42 AM

Thanks Highbarger

If my column has different dates -
Do i just select all of the column and put in -

"> 14 days"

??
Thanks
Carl

**highbarger** · 07-02-2009, 06:13 AM

No, set the conditional formatting for the first cell. Then with that cell selected, click on the Format Painter button and drag it over all the cells with dates that you want the same format applied to.