Formula to calculatie aging then format cells over a specific date

Alaska_A

New Member
Joined
Jul 21, 2015
Messages
5
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!!!
 
Last edited:

DILIPandey

Well-known Member
Joined
Jul 25, 2013
Messages
1,336
Hi Alaska_A,

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

Regards,
DILIPandey
 

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
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")
 

Forum statistics

Threads
1,085,432
Messages
5,383,634
Members
401,843
Latest member
stevensmith1

Some videos you may like

This Week's Hot Topics

Top