# Formula Help DOB

#### goju

##### Board Regular
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.

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

#### Krishnakumar

##### Well-known Member
Hi,

Try,

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

or

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

where A1 houses the DOB

HTH

#### Peter_SSs

##### MrExcel MVP, Moderator
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
ABCD
1DofBJ/S (based on Toady)J/S (based on 1 Jan this year)
201/01/2000JJ
301/01/1989SS
429/11/1989SJ
530/11/1989JJ
601/01/1989SS
7
Age from DOB

#### BrianB

##### Well-known Member
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 =
Code:
``=IF(MONTH(NOW())<MONTH(A1),YEAR(NOW())-YEAR(A1)-1,YEAR(NOW())-YEAR(A1))``
and then your formula referring to that.

#### goju

##### Board Regular
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

#### Peter_SSs

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

Replies
3
Views
115
Replies
6
Views
681
Replies
3
Views
134
Replies
3
Views
186
Replies
3
Views
281

1,195,618
Messages
6,010,730
Members
441,566
Latest member
spimcom

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back