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

#### jennifersc

##### New Member
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!

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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
ABC
201/06/2012201/06/2014
306/06/2012406/06/2016
402/06/2012402/06/2016
507/06/2012307/06/2015
603/06/2012303/06/2015
708/06/2012408/06/2016
804/06/2012404/06/2016
909/06/2012209/06/2014
1005/06/2012405/06/2016
1110/06/2012310/06/2015
1206/06/2012206/06/2014
1311/06/2012311/06/2015
1407/06/2012407/06/2016
1514/06/2012414/06/2016
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C21. / Formula is =\$C2<=TODAY()Abc
C31. / Formula is =\$C2<=TODAY()Abc
C41. / Formula is =\$C2<=TODAY()Abc

Last edited:
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'

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!

Replies
3
Views
304
Replies
0
Views
725
Replies
8
Views
290
Replies
10
Views
748
Replies
1
Views
338

1,206,971
Messages
6,075,922
Members
446,170
Latest member
zzzz02

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back