Conditional formatting formula for date depending on name

[HaudYerWeesht]

I have a training spreadsheet and in column I, I have conditional formatting so when the date expires it turns red - =TODAY()-I6>=365

However I have more than one training course on the spreadsheet in column G which expire at different periods. Security awareness expires after one year but environmental awareness expires after 3 years. Is there a formula that can incorporate the TODAY formula depending on the name of the course?

Thank you

 

[Joe4]

Sure. Probably use the VLOOKUP formula to lookup the expiration period associated with a training course, and then return that value. If it is in years, just multiply that by 365 in your formula.
So your formula may look something like:

[COLOR=#333333] =TODAY()-I6>=(365*VLOOKUP(...))[/COLOR]

If you need help on VLOOKUP, see: https://exceljet.net/excel-functions/excel-vlookup-function

 

[Scott T]

Maybe something like

Book1
	H	I	J	K	L	M
2	Security awareness	5/26/2018		TRUE		Security awareness
3	environmental awareness	5/26/2018		FALSE		environmental awareness
4	training7	2/7/2019		TRUE		training1
5						training2
6						training3
7						training4
8						training5
9						training6
10						training7
Sheet1

Cell Formulas
Range	Formula
K2	=TODAY()-I2>=CHOOSE(MATCH(H2,$M$2:$M$10,0),365,1095,730,30,44,55,650,77,90)
K3	=TODAY()-I3>=CHOOSE(MATCH(H3,$M$2:$M$10,0),365,1095,730,30,44,55,650,77,90)
K4	=TODAY()-I4>=CHOOSE(MATCH(H4,$M$2:$M$10,0),365,1095,730,30,44,55,650,77,90)

list all the course somewhere and then in choose formula list the days in the same order as you list

 

[HaudYerWeesht]

Works a treat, thank you so much

 

[Joe4]

Works a treat, thank you so much

I don't understand - you weren't the one who asked this question.
Did you intend to post to another thread?

 

[HaudYerWeesht]

Sorry. I forgot the log in details from my first account so I opened a new account then I started this thread with the original details but somehow when I click the link from the email it shows the new details. I don’t know why!