Combining 2 formulas in 1 cell - add years to a date and change the color of the cell if older than 3 yrs

[jennifersc]

I'm new to this group and to complex formulas in Excel. I don't know a thing about macros or VBA.
What I have:
column A is a date (mm/dd/year) equipment purchased
column b is a number: the number of years the piece of equipment is going to be kept for
column c is where I need help. I would like this cell to add the number of years from B2 to A2. So A2+B2=C2; giving me a date to replace the equipment.
THEN I need that same C2 to fill in RED if the date is today or in the past.

The end result being a red cell meaning I need to replace that piece of equipment because we've had it longer than the allowable amount of years.
I hope this makes sense - I've been trying so many things that I'm confusing myself!
Thank you in advance for your help!

 

[MARK858]

In C2 and dragged down

=DATE(YEAR(A2)+B2,MONTH(A2),DAY(A2))

Select from C2 to the last cell in Column C with data.

Click conditional formatting, click highlight cell rules, click more rules, click use a formula to determine which cells to format.

In the box under format cells where this formula is true type " =$C2<=TODAY()" without the quotes.

Then click the box marked Format.. and select the formatting you want.
click ok,ok,apply,OK.

Excel Workbook
	A	B	C
2	01/06/2012	2	01/06/2014
3	06/06/2012	4	06/06/2016
4	02/06/2012	4	02/06/2016
5	07/06/2012	3	07/06/2015
6	03/06/2012	3	03/06/2015
7	08/06/2012	4	08/06/2016
8	04/06/2012	4	04/06/2016
9	09/06/2012	2	09/06/2014
10	05/06/2012	4	05/06/2016
11	10/06/2012	3	10/06/2015
12	06/06/2012	2	06/06/2014
13	11/06/2012	3	11/06/2015
14	07/06/2012	4	07/06/2016
15	14/06/2012	4	14/06/2016
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2	1. / Formula is =$C2<=TODAY()	Abc
C3	1. / Formula is =$C2<=TODAY()	Abc
C4	1. / Formula is =$C2<=TODAY()	Abc

 

[vaskov17]

the formula in C2:

=DATE(YEAR(A2)+B2,MONTH(A2),DAY(A2))

fill down as necessary.

To get the red color, select the entire column C or whatever cells you want highlighted and in the toolbar go to 'Conditional Formatting' -> 'Highlight Cells Rules' -> 'Less Than' -> then in the left field type without the quotes '=today()+1'

 

[jennifersc]

Thank you so much! This is perfect! OMgoodness, I have a splitting headache from trying to figure this out and you do it in a flash! You rock! Thank you again!