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!

 

[bpgolferguy]

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)))

Perfect!!!! Thank you thank you thank you!!!!

 

[bpgolferguy]

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)))

This worked great! Is there anyway you can make it so that if the A2 is blank then it does nothing in A3? Right now it puts 1900 in there. I would just like it to be blank. Thanks!

 

[texasalynn]

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

 

[bpgolferguy]

Thank you! Could you also show what it would be for the first code? In A2?

 

[texasalynn]

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

Excel 2003

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

 

[bpgolferguy]

You're a lifesaver....thank you!