INT function help

[heyz123456]

Hello,

I request anyone's help in this simple sounding, but difficult (for me) formula problem.

I was given a population and I would need to calculate their age using their birthday and what date they died. For the most part, I would have their Date of Birth (mm/dd/yyyy) and Date of Death (mm/dd/yyyy) and I used the following formula:

=INT(YEARFRAC(A1,B1)) - A1 represents Date of Birth and B1 is Date of Death.

But here is where I need help.

For some of the population, I have their Date of Birth but not their Date of Death and for others, I would have their Date of Death but not their Date of Birth. These individuals would still need to be recorded in the spreadsheet but what seems to happen when I input the Date of Birth but not the Date of Death, it calcuates from 1900 and give me a squewed age. A similar calucation occurs if i have the Date of Death and not the Date of Birth.

What I would like is two options: if I have Date of Birth but not Date of Death or vise versa I would like the age to remain as 0;
and

If have Date of Death but not the Date of Birth or vise versa, I would like the age to calcualte as N/A

How do I put that in a formula?


Thanks

[JohnTopley]

if I have Date of Birth but not Date of Death or vise versa I would like the age to remain as 0;

and

If have Date of Birth but not the Date of Death or vise versa, I would like the age to calcualte as N/A

.... so which one?

=IF(OR(DoB="",DoD=""),0,DoD-DoB)

=IF(OR(DoB="",DoD=""),"#N/A",DoD-DoB)

[joeu2004]

=INT(YEARFRAC(A1,B1)) - A1 represents Date of Birth and B1 is Date of Death.
[....]
if I have Date of Birth but not Date of Death or vise versa I would like the age to remain as 0;
and
If have Date of Birth but not the Date of Death or vise versa, I would like the age to calcualte as N/A

Typo? I presume the last should read: if DoD, but not DoB, then N/A.

Not clear if you mean literally "N/A" or the Excel error #N/A. I presume the first.

=IF(B1="", 0, IF(A1="", "N/A", INT(YEARFRAC(A1,B1))))

[heyz123456]

Thank you Joeu2004, for providing me with the full function equation so that I can simply input in my excel cell. Also, you were correction in both presumptions.

[heyz123456]

Sorry JohnTopley for being a bit confusing, it was the former that I wanted.

[joeu2004]

Thank you Joeu2004, for providing me with the full function equation

You're welcome. But on second thought, perhaps the following is better for you:

=IF(B1="", IF(A1="", "", 0), IF(A1="", "N/A", INT(YEARFRAC(A1,B1))))

That way, if both A1 and B1 appear blank (no data yet), the formula returns the null string (""), so that the cell also appears blank. Good for building "templates".