Formula Help DOB

goju

Board Regular
Joined
Dec 15, 2004
Messages
133
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
Joined
Feb 28, 2003
Messages
2,615
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
 
Upvote 0

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
59,808
Office Version
  1. 365
Platform
  1. Windows
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
 
Upvote 0

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
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.
 
Upvote 0

goju

Board Regular
Joined
Dec 15, 2004
Messages
133
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
 
Upvote 0

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
59,808
Office Version
  1. 365
Platform
  1. Windows
Try:
=IF(O119="","Unknown",IF(DATEDIF(O119,TODAY(),"Y")<16,"J","S"))
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top