# Basic Excel help needed

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

Banned

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

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

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

Original Poster

andrpro;4360035

http://www.fontstuff.com/excel/exltut01.htmto 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(C2blank")))

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
=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?

For the 16-18 column put in
=IF(AND(16For 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

Original Poster

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

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!

Original Poster

ChipSticks;4360742

lolol@ BASIC... I just did my level 2 in Excel and I have no idea what … lolol@ BASIC... I just did my level 2 in Excel and I have no idea what you're talking about.... lolSomeone 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?

sancho1983;4361311

Anybody any ideas on how to get it to display the date they will turn 19? … 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
@
Text
Top Discussions

See more discussions

Top Discussions

See more discussions