Calculating Age with a Year and Date of Birth - Strange Results

[QBERT]

I have a Date of Birth and a Current Season Year and am using the =DATEDIF(Date of Birth, Year, "y") formula to calculate age. For some reason I cannot figure out it works fine for half of my 500,000 row data set. But the last 250,000 points do not work. Here is an example

Column 1: Date of Birth "11/13/75" = This is a VLOOKUP from another tab that is formatted as a date.
Column 2: Season "2001"
Column 3: Age. This returns a value of 70 years old, which is not correct. The formula for the calc is listed above using DATEDIF formula.

Column 1 is formatted as Date
Column 2 is formatted as General
Column 3 is formatted as General

Please advise. Thanks.

Q

 

[jtakw]

Hi,

Your argument for the "End Date" has to be Real Date, Not just the year.
Look here:

DATEDIF function - Microsoft Support

Calculates the number of days, months, or years between two dates.

support.microsoft.com

 

[AhoyNC]

If all you have is the year in column 2 try:
Column2-YEAR(column1)

See example below:
The reason you are getting 70 is your date 11/13/75 is actually a number of 27711 (general format).
When DATEDIF subtracts the year 2001 it gets 24710. then when you divide that by 365 you get 70 years.

Book1
	A	B	C
1			General Formate
2	Date	11/13/1975	27711
3	Year	2001	25710
4	Divide by	365	70.44
5
6		26
Sheet1

Cell Formulas
Range	Formula
C2	C2	=B2
C3	C3	=C2-B3
C4	C4	=C3/365
B6	B6	=B3-YEAR(B2)

 

[QBERT]

Thanks! I made that correction and now my actual age calculation gives me a NUM error. I am looking at formats to make sur everything is Date except for the calculation answer.

 

[QBERT]

Thanks! I made that correction and now my actual age calculation gives me a NUM error. I am looking at formats to make sur everything is Date except for the calculation answer.

Solved. Thank you for your help everyone!

Q

 

[jtakw]

You're welcome, glad you worked it out.