How to keep Column D blank if Column C is blank. If Column C has date, use formula for Column D

[gingerfauxreal]

I have been researching this nearly all day and am struggling to find a solution. I have attached a picture of the columns I am working with.

The "Age" column, which is column D, has a formula to calculate ages in years and months from the DOB given in column C. The formula in column D is as follows:

=DATEDIF(C3,TODAY(),"y")&" yrs, "&DATEDIF(C3,TODAY(),"ym")&" mos "

As you can see, when column C doesn't have any data, column D still tries to calculate an age.

I need a formula that leaves cells in column D empty when the cells in column C are empty. BUT, I also need the formula listed above to work when there is a DOB in column C. I, also, need to know how to use conditional formatting to highlight entire rows when the age is equal to or over 26.

I have fiddled with conditional formatting to no avail. I have tried creating my own formulas by tinkering with the ones I found online, and, not surprisingly, they don't work. To me this seems extremely complicated, and, at this point, I'm not even sure Excel can do such a thing.

I have more questions if this gets answered, so if you help, you may be in for the long haul. haha

Thank you.

 

[Joe4]

Welcome to the Board!

I need a formula that leaves cells in column D empty when the cells in column C are empty.

Try this:

=IF(C3>0,DATEDIF(C3,TODAY(),"y")&" yrs, "&DATEDIF(C3,TODAY(),"ym")&" mos ","")

And use this formula for your Conditional Formatting calculation:

=AND($C3>0,$C3<=EDATE(TODAY(),-26*12))

 

[gingerfauxreal]

Welcome to the Board!


Try this:

=IF(C3>0,DATEDIF(C3,TODAY(),"y")&" yrs, "&DATEDIF(C3,TODAY(),"ym")&" mos ","")

The fact that it was that simple makes me feel so dumb. I appreciate you! I've been messing with the "IF" function for literal hours.

 

[Joe4]

The fact that it was that simple makes me feel so dumb. I appreciate you! I've been messing with the "IF" function for literal hours.

You are welcome.

The key is to understand exactly how Excel stores dates. It stores them as number, actually the number of dates since 1/0/1900 (so time is just the fractional component of one day).
So dates really are just numbers with special date formats.
Because they are numbers, we can just check to see if the value in that cell is greater than 0 (meaning it contains a valid date).

 

[gingerfauxreal]

And use this formula for your Conditional Formatting calculation:

=AND($C3>0,$C3<=EDATE(TODAY(),-26*12))

THANK YOU. I cannot thank you enough.

 

[Joe4]

You are welcome.
Glad I was able to help!

 

[gingerfauxreal]

You are welcome.

The key is to understand exactly how Excel stores dates. It stores them as number, actually the number of dates since 1/0/1900 (so time is just the fractional component of one day).
So dates really are just numbers with special date formats.
Because they are numbers, we can just check to see if the value in that cell is greater than 0 (meaning it contains a valid date).

That's what I was trying to do. I had seen it on a couple of different discussion boards while on my search today. I just couldn't get the formula right.