Calculating age

[fkheng]

If i have 2 fields in Excel, 1 called "Age" and one called "Date of birth", is it possible to calculate and fill in the Age field based on the date of birth and automatically update itself when it matters? Is there such a thing without coding? Or do i need to apply VBA coding for that?

[Spajeoly]

Just use the DateDiff("Y", DateOfBirth, Date) Function for it. I don't know if you could use a regular formula for it or not, I always use VBA for such deals though.

[fkheng]

okay, is there anyway to detect if a cell is empty? through a formula in excel?

[lintz]

If ActiveCell.Value ="" then
msgbox "Cell is blank"
End If

[opus]

Put that in Cell B1
"IF (A1="";"Empty";"NOT EMPTY)"

But that's not VBA ;-)

[WorkHorse]

You can use DATEDIF Excel formula (no VBA):

=DATEDIF(A1,TODAY(),"y")

"A1" is the cell with the date. "y" mean to express result in years from "TODAY". :)

[fkheng]

excellent, the datediff works! but it fails if the Date of Birth field is empty, there's no validation for an empty cell...

i know opus put a formula here, but i tried i can't get it to work...any idea?

[fkheng]

is there anything wrong with this formula?

this formula will be placed in F19/Age cell, my aim is to calculate the age of people based on their date of birth, i have modified whatever advice you people have graciously offered, i am grateful for the advice u have offered...

=IF(F19="",IF(G19="","",DATEDIF(G19,TODAY(),"y")), "")

F19 is the Age, G19 is the Date of Birth field

Just to interpret what I am trying to do, if F19/Age field is blank, then only I will check, if the G19/Date of birth cell is empty, then the F19 cell will be blank, whereas if there is a date, then it will calculate the age and display it in the Age field...

somehow, there is something wrong as no matter whatever the condition is, the cell outputs a value of '0'...

[fkheng]

help help, i really need to solve this problem!

[opus]

can't check the syntax, since i have no excel on hand,but..

if you put that formula into cell F19, F19 is not empty, and that why you don't get you result! Do only check the second If (G19), since the cell will show nothing if that cell is empty, and I guess that' what you want!

=IF(G19="","",DATEDIF(G19,TODAY(),"y"))

[Spajeoly]

Originally posted by WorkHorse
You can use DATEDIF Excel formula (no VBA):

=DATEDIF(A1,TODAY(),"y")

"A1" is the cell with the date. "y" mean to express result in years from "TODAY". :)

Thanks for clearing that up for me. :D

[fkheng]

i know this is a late reply...
i was busy working on other things, and now i have the time to return to this...i have modified the formula to become this:

=IF(F21<>"",DATEDIF(F21,TODAY(),"y"), IF(E21="","N/A","TEST2"))

F21 = cell containing date of birth
E21 = cell containing age

If there is something in F21 (assuming data for date of birth is valid), then calculate date difference
For nested if, if no date of birth was provided (this is the else part of the first IF), then check to see if E21 is blank, if it is blank, i want to appropriately address the blank by putting "N/A" there, if there is something in E21, i'll output "TEST2"(for testing purposes)

i have tested this formula, and it works for the first IF, if there is a date of birth, it calculates the age...the problem arises with the nested IF, when there is no date of birth, and E21 is blank, it should output "N/A" but it doesn't, instead it outputs '0'...
this formula is used in cell E21, is this because i cannot reference the same cell as the cell where the formula is?

[WorkHorse]

Yes. You can't use a cells value in a formula that determines the same cell's result. When you enter the formula, Excel should bring up a message about "Circular Reference" and the cell value defaults to zero.

[fkheng]

hm.......argh....that's absurd...

do u know of any way i can then use a formula in a cell to change the value of that cell and to also evaluate a condition about that cell?

any special technique? or anyway of using a formula in a cell to re-write a new value to the same cell? is it possible?

[WorkHorse]

hm.......argh....that's absurd...

Why would you want or need to? The value of the cell is the value of the formula itself. You are asking the formula to check the formula, which will check the formula, which will... See you get an endles loop. So you are saying if the formula is one thing, then the formula should be another thing. You can't check the value of a cell formula against the value of the cell formula itself. If you want to check the RETURN VALUE of a formula before returning the result, nest the formula in an IF.

[fkheng]

okay, is there anyway to use an IF statement elsewhere to check if the cell is blank, replace it with a text like "N/A"?

coz my situation here is that although i have 2 fields, age and date of birth, sometimes the date of birth is not provided, but i know the age of the person...
so i have to modify my formula to accomodate situations whereby if there is no date of birth but already an existing age value in the age field, i should not replace it with "N/A" but retain it's existing value...

have any idea on how i can do it? or is my explanation bad...

[WorkHorse]

You have a couple options. If you have a column with ages, if the cell is, say, "25", then that is the formula a well as the value. So you can't change the formula without changing the value itself. You could make a new column for "Calculated Age" (or somehting like that) that uses the formula to check the "Age" and "Birth Date" cells to return the value you want. If you don't want to have a thrid column and just always have the "Age" column show the age (whether it be the existing value of th cell or calculated fromthe birth date) then you would need to do that with a VBA program. Note that even a VBA function cannot do a circular reference, because if you change the formula of the cell, it will no longer be the age value, and therefore the VBA function is no longer the formula. With a VBA function, it would be a one time run through of all cells--they would not update automatically if you changed a birth date.

[fkheng]

yeah, wat u said is true about VBA, it would be running thru onli once, and i guess formulas also have limitations...

looks like the onli way here is to have a third column, in my actual situation, might not be practical or professional to do that...will consider that...

thank you so much for your help and advice, will come back here once i face more obstacles...thanx!

[opus]

Let' see if I get your problem correct:
You want two columns, one with the age and one with the date of birth.

In case A you just input the date of birth, and EXCEL is calculating the age (using the formulas from above, only ONE IF!).

Case B would be , you have no date of birth, in this case EXCEL leaves the age empty (still no problem with the formulas!)

Case C is, you enter a age directly into the age column, in that case you "overrule" the formula that was in that cell (you are actually deleting it with the input). The result is you se just the age.

Isn't that what you want?

[fkheng]

yeap, correct, but becoz of the problem of circular reference, i'm finding it hard to solve or create an appropriate formula, u know how i should correct or modify the formula appropriately?