Conditional Formatting for service award

[ninjapaping]

hello! i have a column of dates the employees were hired. i want to format these when they reach 10 years as of today and every 5 years thereafter. what formula should i use in conditional formatting?

 

[PeteWright]

Hello @ninjapaping
You did not mention what kind of format you were thinking of, so here is just one example with some additional helper cells:

TEST.xlsm
	A	B
1	entry date	years employed (5-year-cycle)
2	3-Jun-2013
3	17-Aug-2009
4	14-Jun-2018
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	= ((YEAR(TODAY())-YEAR(A2))-MOD(YEAR(TODAY())-YEAR(A2), 5))/5

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B4	Other Type	DataBar		NO

 

[ninjapaping]

thanks for responding @PeteWright

but this is not what i want. i don't want to add another column. i just want to color the date if the employer reaches 10 years of service as of today and will color again every 5 years thereafter, that is 10yrs, 15yrs, 20 yrs and so on. will that be possible?

 

[MARK858]

Maybe something like...

Book1
	A
2	03/06/2013
3	17/08/2009
4	14/06/2018
5	15/06/1999
6	16/06/2003
7	17/06/2018
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A7	Expression	=A2<=EDATE(TODAY(),-(12*25))	text	YES
A2:A7	Expression	=A2<=EDATE(TODAY(),-(12*20))	text	YES
A2:A7	Expression	=A2<=EDATE(TODAY(),-(12*15))	text	YES
A2:A7	Expression	=A2<=EDATE(TODAY(),-(12*10))	text	YES

 

[ninjapaping]

Maybe something like...

Book1
	A
2	03/06/2013
3	17/08/2009
4	14/06/2018
5	15/06/1999
6	16/06/2003
7	17/06/2018
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A7	Expression	=A2<=EDATE(TODAY(),-(12*25))	text	YES
A2:A7	Expression	=A2<=EDATE(TODAY(),-(12*20))	text	YES
A2:A7	Expression	=A2<=EDATE(TODAY(),-(12*15))	text	YES
A2:A7	Expression	=A2<=EDATE(TODAY(),-(12*10))	text	YES

thanks for this input @MARK858

but to be more specific, i just want to color the text blue in bold format when that employee reach 10 years as of today and revert it to default formatting when it reaches 11 years. then format blue in bold text again when that employee reaches 15years of service, the in 20, 25, and so on. that is, if the length of service in years is not equal to 10, 15, 20 and so on, then the format should be in default. thanks.

 

[MARK858]

Formula for 10 years, change the 10 and 11 for each other category, format to suit.

=AND(A2<=EDATE(TODAY(),-(12*10)),A2>EDATE(TODAY(),-(12*11)))

 

[ninjapaping]

Formula for 10 years, change the 10 and 11 for each other category, format to suit.

=AND(A2<=EDATE(TODAY(),-(12*10)),A2>EDATE(TODAY(),-(12*11)))

it worked, but it covers all days between 10th to 11th year. let me request more, let's just focus on the year. that is todays year is 2024, i want to format all employees hired in 2014, 2009, 2004 and so on. thanks for your patience.

 

[MARK858]

That is a different question that I don't have time to look at for now, I'll look at it if no-one has replied after I finish work tonight

 

[MARK858]

Actually you could probably use something like

=YEAR(A2)=YEAR(TODAY())-10

and change the 10 for each category

 

[ninjapaping]

Actually you could probably use something like

=YEAR(A2)=YEAR(TODAY())-10

and change the 10 for each category

HAHA. I JUST FINISHED MODIFYING YOUR PREVIOUS FORMULA. AND I GOT WHAT I WANT
=AND((YEAR(L2)+10)=YEAR(TODAY()))
LOOKS THE SAME THOUGH. THANKS BUDDY.

JUST ONE LAST THING, CAN WE JUST COMBINE THIS TO INCLUDE ALL CATEGORIES 10, 15 , 20 ... IN JUST ONE FORMULA?