If/Then formula using text and dates

[Danandem]

I have three cells I want to use, for this purpose I'll go with A1, B1, and C1. If A1 has the word (SECRET), then I want C1 to add ten years to the date in B1 and if the word in A1 says TOP SECRET, then I want C1 to add five years to the date in B1. If there is nothing in A1, then I want C1 to be red in color.

 

[lenze]

Hi and welcome to the board!!

=IF(A1="Search",DATE(YEAR(B1)+10,MONTH(B1),DAY(B1)),IF(B1<>"",DATE(YEAR(B1)+5,MONTH(B1),DAY(AB1)),""))

Use Conditional formatting to color C1 Red when A1 = ""
lenze

 

[Danandem]

Still not working. Can you put the words SECRET and TOP SECRET in the appropriate spots to show me how it reads. Using your formula, I replaced the word serach with Top Secret and put the word Secret where I thought it should go. Don't know if you putting B1 (I put it in bold) instead of A1 was a mistake or not. I included your formula (top) and how I put the data in (bottom). Any help would be greatly appreciated. Thank you!

=IF(A1="Search",DATE(YEAR(B1)+10,MONTH(B1),DAY(B1)),IF(B1<>"",DATE(YEAR(B1)+5,MONTH(B1),DAY(AB1)),""))


=IF(A1="Top Secret",DATE(YEAR(B1)+10,MONTH(B1),DAY(B1)),IF(A1="Secret",DATE(YEAR(B1)+5,MONTH(B1),DAY(AB1)),""))

 

[lenze]

The B1 was a typo. Should have been A1. So was "Search". I assumed Secret and Top Secret where the only choices were "Secret" and "Top "Secret". You can use

=IF(A1="Secret",DATE(YEAR(B1)+10,MONTH(B1),DAY(B1)),IF(A1="Top Secret",DATE(YEAR(B1)+5,MONTH(B1),DAY(B1)),""))

lenze

 

[Danandem]

Awesome. Thank you very much!!!!!!!!!!!!

Follow on question, if you don't mind? I have two excel spreadsheets, one is my tracking/working sheet and the other is a higher headquarters. I want the data in my sheet to automatically update the other sheet without having to set the formula line by line or copy/paste the entire section. I have over 800 people I track! The higher headquarters has the different units on seperate tabs/sheets.