Formula to calculatie aging then format cells over a specific date

[Alaska_A]

After reading through several threads on aging formulas, I wasn't able to find a way to achieve the following.

I am looking for a formula that calculates the length of employment based on the anniversary date enter in another cell (D11) on the same sheet. Then a condition that changes the cell to red fill with black bold text after 5 years.

Is there also a way to have a cell (H11) say no if the aging hasn't reached the 4.5 year mark, then say yes once it has been 4.5 years?

Sorry the request is so convoluted, I was having difficulty articulating what I am looking for.

Thank you!!!

 

[DILIPandey]

Hi Alaska_A,

Can you share a sample data here ?
Mention your expected results as well.

Regards,
DILIPandey

 

[FDibbins]

What you need to understand about dates and times in excel is…

a date is just a number representing the number of days passed since 1/1/900...and then formated in a way that we recognise as a date. So, for instance, today (Mon 28 Mar 2016) is actually 42457

Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75

with that said, to get the date difference, just subtract 1 from the other, and format as general - that will give you days. If you want to show years/months/days, then try this...
=IF(DATEDIF(A1,B1,"y")=0,"",DATEDIF(A1,B1,"y")&" years ")&IF(DATEDIF(A1,B1,"ym")=0,"",DATEDIF(A1,B1,"ym")&" months ")&DATEDIF(A1,B1,"md")&" days"

For the color change, you woukd use Conditional Formatting, something like this...
1. highlight the range you want to apply the conditional formatting to
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter =cell-to-color>=EDATE(d11,60)

For the last part, use that same function...
=IF(cell-to-test>=EDATE(d11,54),"Yes","No")