# Thread: Excel date formula (Excel)

1. ## Excel date formula (Excel)

You guys have done an outstanding job in finding me two formulas already, this is great. I have one more for you, my apologies for the seemingly simplicity of this question:

1. Based on the attached sample worksheet, I am trying to figure a formula to calcuate the age of the annuitant at the date the application was signed.

Thanks again!
L

2. ## Re: Excel date formula (Excel)

The formula is rather long because of error checking (you have missing values, invalid dates etc.). In C2, enter the formula

=IF(OR(ISBLANK(A2),ISBLANK(B2),ISERROR(YEAR(B2)-YEAR(A2))),"",YEAR(B2)-YEAR(A2)-(MONTH(B2)<=MONTH(A2))+AND(MONTH(B2)=MONTH(A2),DAY (B2)>=DAY(A2)))

and fill down as far as needed.

3. ## Re: Excel date formula (Excel)

Hans,

Does the formula change if I edit the information to make a 'clean' spreadsheet?? See attached.

Thanks!
Lori

4. ## Re: Excel date formula (Excel)

Sorry, I don't understand the question, and the workbook looks the same as before.

5. ## Re: Excel date formula (Excel)

The same formula should still work fine.

6. ## Re: Excel date formula (Excel)

For fun I thought I may give you an alternative solution <img src=/S/grin.gif border=0 alt=grin width=15 height=15>. This solution will give you give you their age in years, months and days:

=IF(B2="","",DATEDIF(A2,B2,"y") & " years, " & DATEDIF(A2,B2,"ym") & " months, " & DATEDIF(A2,B2,"md") & " days")

Enjoy.

7. ## Re: Excel date formula (Excel)

Ah yes, DATEDIF is much easier.

For others reading this: DATEDIF is not documented in most Excel versions. A description can be found on Chip Pearson's site: DATEDIF Function.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•