Calculating age in spreadsheet

Hi Maarten,

Calculating age is very simple, provided you want to express the answer in the Mac clock's basic unit, the Day.

With the birthdate in cell A1, enter this formula in the cell you want to display the age:

=INT(NOW())-A1

NOW() returns a serial number indicating the current time (in Days and fraction of Day) according to the Mac's clock at the instant the formula was called. For example, at 3:32:04 PM on Feb 5, 2008, NOW() returned 38021.647269. INT() strips the fractional part (which represents the time of day), leaving only the whole number (date) part.

Converting that result from Days to Years, Years and Months, or Years, Months and Days requires dealing with the vagaries of the calendar. More on that to follow.

Regards,
Barry

More...

Maarten wants to calculate the age as of 'today'. Others may want the age on a specific date. To handle both these cases, I'll put the 'as of' date in cell A1, and reference that cell in formulas. Date of Birth (DOB) will be recorded in column A, starting at A3.


Enter =NOW() in cell A1 (for calculation of age as of 'today') OR Enter a specific 'as of' date in B1

Enter the (first) DOB in cell A3.

_Calculation of age in years._

Enter the following formula in B3:

=IF(A3,YEAR(A$1)-YEAR(A3)-((31 MONTH(A3)DAY(A3))>(31MONTH(ADAY(A3))>(31MONTH(A$1))DAY(A$1))),"")

--The IF statement keeps the cell containing the formula blank until something is entered in cell A3.
--The part between the commas calculates the age in years by first subtracting the YEAR extracted from the DOB from the YEAR extracted from A1, then applies a correction, subtracting 1 if the current date is before the anniversary of the birth in the current year.

_Caclulation of additional months_

Enter the following in C3:

=IF(A3,MONTH(A$1)-MONTH(A3)+12*((MONTH(A$1)-MONTH(A3))<0)-(DAY(A$1)<DAY(A3)),"")

--The IF statement performs the same function as above.
--The part between the commas calculates the remaining months of the age by first subtracting the MONTH extracted from the DOB from the MONTH extracted from A1, then applies two corrections, first increasing the value by 12 if the initial result in negative, then subtracting 1 if the 'as of' day of the month is less than the day of the month of the DOB.

Regards,
Barry

Hi Maarten,

_Formula troubleshooting_:

A TRUE or FALSE result result means the formula is comparing two values and returning the result of the comparison.

In the formula you came up with, the only comparison operator is the ">" sign, so the formula must be comparing what's in front of that to what follows it.

=JAAR(A1)-JAAR(A3)-(31*MAAND(A3)+DAG(A3)) > (31*MAAND(A1)+DAG(A1))

What the formula should be doing is two subtractions. First subtracting the second term ( JAAR(A3) ) from the first ( JAAR(A1) ), then subtracting the result of the comparison in the rest of the formula.
Here's your revision and my original (with the IF() statement removed). I've added some spaces to yours to make the terms line up a bit better.

=JAAR(A1)-JAAR(A3) - (31 MAAND(A3)DAG(A3))>(31MAAND(A1)DAG(A1))
=YEAR(A$1)-YEAR(A3)-((31 MONTH(A3)DAY(A3))>(31MONTH(ADAY(A3))>(31MONTH(A$1))DAY(A$1)))

The only difference (other than the $, discussed below) is a pair of brackets around everything following the second "-" sign. Those brackets limit the comparison to what's between them, and make the result of that comparison (which will be either 1—TRUE— or 0—FALSE) the amount to be subtracted from the previous part of the formula.

The function of the "$" sign in the formula is to fix the cell reference to a specific column or row.

In this case, I've assumed you'll have a list of birthdates (in A3, A4, A5, etc.) and a single date (in A1) on which you want to calculate all of the ages.

After you've entered the formula into B3, you can copy it into the rest of the cells in column B using Fill Down (Select the cells to contain the formula, including B3, then go Calculate > Fill Down).

Without the $, the A3 cell references in the initial formula will be changed to reference a cell in the same relative position with respect to the formula's 'new' location.

Example: in B3: =A3
Fill down to B5: =A5

The $, placed before the row reference fixes that reference to the initial row.

Example: in B3: =A$1
Fill down to B5: =A$1

Placed in front of the column reference ($A1) the $ fixes the column when a formula is filled right.
Placed in front of both column and row references ($A$1), the cell reference remains fixed when the formula is filled down or right.

Regards,
Barry

Hello

I think you use comma for decimal separator in Netherlands, in which case you should use semicolon for argument delimiter in AppleWorks' functions.

E.g.

=ALS(A3;JAAR(A$1)-JAAR(A3)-((31 MAAND(A3)DAG(A3))>(31MAAND(ADAG(A3))>(31MAAND(A$1))DAG(A$1)));"")

Please see AW's help for details of function syntax in your language.
Hope this helps,
H

Hello

it seems that you didn't take care that the decimal separator in your country is not the period but the comma.
So the operand delimiter is not the comma but the semi-colon.

May I tell you that I am really surprised that you didn't realize that the delimiters used by Barry aren't those used in your country.

May you try:

in B3 =ALS(A3;JAAR(A$1)-JAAR(A3)-((31 MAAND(A3)DAG(A3))>(31MAAND(ADAG(A3))>(31MAAND(A$1))DAG(A$1)));"")

in C3 =ALS(A3;MAAND(A$1)-MAAND(A3)+12*((MAAND(A$1)-MAAND(A3))<0)-(DAG(A$1)<DAG(A3));" ")

No, I don't know Dutch, I just installed the Dutch localized version and asked it to open the sheet built in English 😉

Yvan KOENIG (from FRANCE jeudi 7 février 2008 16:45:16)

Hello HIROTO

Glad to meet you here.

I arrived some minutes late but I have the two formulas 😉

Message was edited by: KOENIG Yvan

Thanks, Yvan & Hiroto, for jumping in with that. Use of the comma as a decimal separator is pretty much off the radar for us insular North Americans. 😉

Maarten, don't hesitate to ask about that other 'impossible case.'

Regards,
Barry

Hello Barry

Maybe it's why we met several annoying oddities with localised versions of Numbers 😉

but, it's an other story …

Yvan KOENIG (from FRANCE jeudi 7 février 2008 20:53:25)