Date problem

[colkas]

Hi

We have a date of purchase for an item in one column. It as a warranty of 3 years. I want to set a column where it says how long is left on the warranty (perhaps in months)based on the purchase date if it is out of warranty in 3 months to display in green , if out of warrnaty to display in red.

Any ideas how I can achieve this.

Many thanks

 

[gizmo1973]

Format cells as date and then put the equation =a1+1095 in cell b1 (1095 = 365 * 3 is 3 years)
(assuming your date is in cell a1)
This will show the date of waranty expiry.
Then you need to conditionally format that cell to colur to your criteria (you state 3 years at one point and 3 months at another so not entirely sure when you want the cell to go red)

Regards, Phil.

M.U.F.C. Show your true support here:

http://www.shareholdersunited.org/join.php

"Shares not Shirts

 

[xlbo]

Have a look at the DATEDIFF FAQ on this forum for more info on dates and reporting on them - specifically on teh difficulties of reporting the number of non standard units (that'd be months) until completion

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question