Age/Date Formula

[bpgolferguy]

Hi, I'm trying to come up with a way to make a "Class of" calculation....you know, like class of 2011, etc? What I'm thinking in my head, and it seems right, is if I have the birthday in A1, then the date they turn 18 in cell A2.....have the formula look at that date (only the month and day) in A2 and if it's before (prior) to 6/1 then use the year (yyyy format) from A2 and input it into A3 as my value. However, if the date in A2 is AFTER 6/1, then I want to add 1 year to the date in A2 and input that year (yyyy) into A3.

The reason I use 6/1 is because that's the official "start" date of my organization each year, so depending on which side of it the birthday falls will determine "Class of" date.

Thanks in advance for the help!

 

[jasonb75]

Try

="Class of "&YEAR(A1+214)

 

[bpgolferguy]

I tried it, but don't think it worked. It returned 1994 for someone born in 1993. Did you mean use A2? The date they turn 18? If so, how will that factor in the 6/1 date?

 

[bpgolferguy]

Also, I don't need it to return "Class of" in the cell....I just need the year (yyyy). Thanks!

 

[texasalynn]

does this work

Excel Workbook
	A
1	7/3/1972
2	7/3/1990
3	1991
Sheet1

Excel 2003

Cell Formulas
Range	Formula
A2	=TEXT(MONTH(A1)&"/"&DAY(A1)&"/"&YEAR(A1)+18,"mm/dd/yyyy")+0
A3	=IF(TEXT(MONTH(A2)&"/"&DAY(A2),"mm/dd")<="06/01",YEAR(A2),YEAR(EDATE(A2,12)))

 

[bpgolferguy]

Help?

 

[texasalynn]

did you look at my post?

 

[barry houdini]

Jason's suggestion should work for you if you reference A2

=YEAR(A2+214)

214 is the number of days from 1st June until 1st Jan so it automatically gives you the right year. This would do the same

=YEAR(A2)+(MONTH(A2)>5)

 

[jmthompson]

slight change to texaslynn's formula

=IF(TEXT(MONTH(D2)&"/"&DAY(D2),"mm/dd")<="06/01",YEAR(D2),TEXT(EDATE(D2,12),"YYYY"))

 

[jasonb75]

Try

in A2 =TEXT(EDATE(A1,216),"mm/dd/yyyy")

in A3 =YEAR(A2+214)