Basic Excel help needed - HotUKDeals
Get the HUKD app free at Google Play

#### Search Error

An error occurred when searching, please try again!

# Basic Excel help needed

7y, 8m agoPosted 7 years, 8 months ago
Don't laugh, I know it's basic but i don't really use Excel and i need to make something for my boss!

I have a table that contains names and dates of birth, i need a formula to convert the Date of Birth DD/MM/YYY into age.

I need to then show which of the people are 16-18 and which are 19+, so i thought i would have two columns; 16-18 and 19+. What formula would i use so it would display a tick or a cross or something in the relevant column?

Hope that makes sense!!

eternal gratitude and rep for people who can help me!

thanks
Tags:
7y, 8m agoPosted 7 years, 8 months ago
Options

(10)
banned 1 Like #1
you'd need more than formula to do that..you need to add buttons for a start!

far easier to stick an IF statement in and use conditional formatting
1 Like #2
http://www.fontstuff.com/excel/exltut01.htm

to convert DOB to current age
1 Like #3
In one cell type the below formula.

=today() (this will display today's date)

For this example we will presume your data (the person's date of birth) is in cell A1 and the today formula is in B1.

Now in the cell where you want the result type =(A1-B1)/365

This will give you their age, now for ease I would use conditional formatting to colour people older and younger than your desired age.
banned#4
If you put their DOB in say Column A Cell 1 then if you use =today() thiswill update daily OR just use a set date in column B Cell 1. Then in Column C Cell 1 put =(B1-A1)/365 format it so it shows as a number with 0 decimal places.

Thats the most basic thing I can think of

Obvo you can decide which cells you want it in

u sure u need buttons?
EDIT: As above
#5
andrpro
http://www.fontstuff.com/excel/exltut01.htm

to convert DOB to current age

Thanks to all, i used that site and got this

=YEAR(NOW())-YEAR(B2)-IF(MONTH(B2)>MONTH(NOW()),1,IF(MONTH(B2)=MONTH(NOW()),IF(DAY(B2)>DAY(NOW()),1,0)))

Which i understand less than any of. But it works :)

I don't know what you mean by buttons :oops: I thought something like..

If(C2<18),(yes),("blank")))

Does that mean if the Contents of C2 (the age) is less than 18 then display 'yes', if it isn't then display 'blank'.

Like i say, i know nothing, that's just how my brain thinks :thinking:

Edit -

=IF(C2<19,"yes","") will display 'Yes' in the 16-18 column if they are under 19

=IF(C2>=19,"yes","") will display 'Yes' in the 19+ column if they are 19 or over

I'm happy with that.

Now for the kicker.....

If they are under 19 i need it to display the date they will turn 19.

Is that easily acheivable?
1 Like #6
For the 16-18 column put in
=IF(AND(16<=INT((NOW()-B3)/365.25),(INT((NOW()-B3)/365.25)<=18)),"Yes","No")
For 19+ use
=IF(INT((NOW()-B3)/365.25)>18,"Yes","No")
Where B3 is the reference to the DOB.
Will put Yes in the appropriate column (or no in both if <16)
#7
To get it to display when they would turn 19 could i do

=IF(D2="Yes","'This is the bit i need to work out when they turn 19 but can't","")

D2 being the 16-18 column, so if they are 16-18 then it should display the date they turn 19, i.e DOB + 19 years. can't figure it out though :)
#8
lolol@ BASIC... I just did my level 2 in Excel and I have no idea what you're talking about.... lol

Someone come sit with me on the dumb couch... i have popcorn!
#9
ChipSticks
lolol@ BASIC... I just did my level 2 in Excel and I have no idea what you're talking about.... lol

Someone come sit with me on the dumb couch... i have popcorn!

Lol, I thought i would get laughed at because IF statements were basic stuff. I barely understand most of it!!

Anybody any ideas on how to get it to display the date they will turn 19? ie DOB plus 19 years?
#10
sancho1983

Anybody any ideas on how to get it to display the date they will turn 19? ie DOB plus 19 years?

=DATE(YEAR(A1)+19,MONTH(A1),DAY(A1))

where A1 is DOB