Formula Help DOB

[goju]

i am trying to automaticallly generate a letter which is determined by date of birth,

if in column a the date of birth is 01/01/00, then i need column b to automatically identiyfy that the person is under 16 years of age and put the letter J in column b

If the date was 01/01/89 (ithink thats right) then i need column b to identyfy that ther person is 16 and over and the column automatically put the letter S

the below formula i used on an old spread sheet but this was determined by years and not the date of birth.

Any ideas how to solve this???

=IF(X19>16,"S","J")

By the way X19 was the amount of years.

 

[Krishnakumar]

Hi,

Try,

=IF(YEAR(TODAY())-YEAR(A1)>=16,"S","J")

or

=IF(DATEDIF(A1,TODAY(),"y")>=16,"S","J")

Install Analysis Toolpak Add-In via Tools->Add-In

where A1 houses the DOB

HTH

 

[Peter_SSs]

goju

If age is calculated as at TODAY then try (see column B):
=IF(DATEDIF(A2,TODAY(),"Y")<16,"J","S")

If age is calculated as at 1 Jan this year then try (see column C):
=IF(DATEDIF(A2,DATE(YEAR(TODAY()),1,1),"Y")<16,"J","S")

Mr Excel.xls
	A	B	C	D
1	DofB	J/S (based on Toady)	J/S (based on 1 Jan this year)
2	01/01/2000	J	J
3	01/01/1989	S	S
4	29/11/1989	S	J
5	30/11/1989	J	J
6	01/01/1989	S	S
7
Age from DOB

 

[BrianB]

The formula to do the calculation in a single cell would be quite long. I would do this in 2 steps, with a cell for Age =

=IF(MONTH(NOW())<MONTH(A1),YEAR(NOW())-YEAR(A1)-1,YEAR(NOW())-YEAR(A1))

and then your formula referring to that.

 

[goju]

the formula below is great

=IF(DATEDIF(O119,TODAY(),"Y")<16,"J","S")

the only thing i didnt count on was, where there is no DOB it is replaced with a -

the above formula returns a #VALUE message,

is there a way of including - in the formula to so that if it it see this that it just returns a "unknown" comment

thanking you for your help

 

[Peter_SSs]

Try:
=IF(O119="","Unknown",IF(DATEDIF(O119,TODAY(),"Y")<16,"J","S"))