how to program the Exipred Date column to show the date 6 months

Hi,

I have been asked to create a spreadsheet with three columns Status - Qualified Date - Expired Date.

The Status column shows a green box with "Status" written in it

The Qualified date column is filled with dates.

I need to know how to program the Exipred Date column to show the date 6 onths later from the Qualified date Column.

and when a column reaches the date 6 month's on, for the status box to turn red with "expired written in it.

Can this be done and if so, could someone show me please.

Thanks

It's not always obvious which date is "6 months on". For 31st August 2007 what date do you expect?

=EDATE(B2,6)

will give you 29th February 2008

EDATE function requires Analysis ToolPak to be installed

Assuming your status is in A2, qualified date in B2 and expired date in C2 then A2 could contain the formula

=IF(C2>=TODAY(),"expired","status")

Use conditional formatting to change the colour to red when "cell value is" ="expired"

Thanks,

I have everything working except the =IF(C2>=TODAY(),"expired","status").. It just shows the function in the cell not the expired or status.

Was there something missed?

Thanks,

I have everything working except the =IF(C2>=TODAY(),"expired","status").. It just shows the function in the cell not the expired or status.

Was there something missed?