Calculating Age in Years and Months

In D2, the formula is :

=IF(ISBLANK($C),"",(DATEDIF(C2,TODAY(),"Y")&"y "&DATEDIF(C2,TODAY(),"YM")&"m "&DATEDIF(C2,TODAY(),"YM")&"d"))

In E2, it is :

=IFERROR(VLOOKUP(C2,look_up :: A:B,2),"")

Apply Fill Down

Yvan KOENIG (VALLAURIS, France) mardi 24 mai 2011 16:18:44

Please :

Search for questions similar to your own before submitting them to the community

To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !

Edit my original look_up table this way :

Yvan KOENIG (VALLAURIS, France) mercredi 25 mai 2011 16:45:23

Please :

Search for questions similar to your own before submitting them to the community

To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !

There are two basic parts to this:

1) Determine the students' age:

This involves establishing a "common" unit for age that avoids non-standard length years and can be compared to a table in item (2). I select to use "days" as the units for a students' age

2) Establish a rule for when a student changes grades

This involves setting up a table that provides a list of ages (on Sept 1 of every year) using the same units as in step (1)

In the image of a sheet, below, the table on the left, "Student Info", allows the user to input:

A) the students name

B) the Date of Birth (DOB) for each student

The table then computes the students age in days (along with some other info). The last column in the this table performs a vlookup of the age into the table on the right, "Year Lookup" to find the year the student should be in.

The main formulas are:

"Year Lookup"

B4 =DATEDIF(D4,$D$3,"D") [Fill down]

D1=IF(B1>0, B1, YEAR(C1))

C1=NOW()

D3=DATE($D$1-A3, 9, 1) [Fill Down]

"Student Info" [Fill all down]

C2=IF(ISBLANK(B2),"",DATEDIF(B2,NOW(), "D"))

D2=IF(ISBLANK(B2),"",INT(DATEDIF(B2,NOW(), "M")/12)&" Yrs "& MOD(DATEDIF(B2,NOW(), "M"), 12)&" Mos")

E2=IF(ISBLANK(B2),"",DATEDIF( B2, Year Lookup :: $D$3, "D")-1)

F2=IF(ISBLANK(B2),"","Year "&VLOOKUP(E2,Year Lookup :: $B$3:$C$16, 2))

If you don't want to type all this in here is a link to my example:

http://public.iwork.com/document/?a=p12259087&d=Student_Year.numbers

Updated hyperlink

Gwyn,

Here's a single table solution. Not necessarily better than using a lookup table, but I submit this for variety.

This approach calculates a specific September 1 date based upon current date, so next year it will calculate Sep 1, 2012 and the school year calculations will update. This rollover will occur on Jan 1.

Here are the formulas:

D1: =TODAY()

E1: =DATE(YEAR(TODAY()),9,1)

E2: ="Age on " & E1

Column D body rows: =DATEDIF($C, TODAY(),"Y") & " Y " & DATEDIF($C, TODAY(),"YM") & " M"

Column E body rows: =DATEDIF($C, $E$1,"Y") & " Y " & DATEDIF($C, $E$1,"YM") & " M"

Column F body rows: =IF(DATEDIF(C,$E$1,"Y")-4>0, "YR"&DATEDIF(C,$E$1,"Y")-4, "")

Regards,

Jerry

The following expression will give you the years and months of age to the current date if the birth date is in Column A. You can make adjustments from there.

=INT(DATEDIF(A,TODAY(),"M")/12) & " Years and "&MOD(DATEDIF(A,TODAY(),"M"),12)&" Months"

As for the grade year calculation, when do you begin talking in terms of the school year that begins in September? Are you today talking about the year just about to end, or the next one?

Regards,

Jerry

Funny

Three answers exactly at same time 😁

Yvan KOENIG (VALLAURIS, France) mardi 24 mai 2011 16:21:21

Please :

Search for questions similar to your own before submitting them to the community

To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !

Because Badunit posted before and edited after.

When I posted my laughing message, the badunit one was empty and stamped 10:18. 😝

Yvan KOENIG (VALLAURIS, France) mardi 24 mai 2011 16:37:17

Please :

Search for questions similar to your own before submitting them to the community

To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !

Gwyn,

You may have missed my question above. I'll repeat.

As for the grade year calculation, when do you begin talking in terms of the school year that begins in September? Are you, today, talking about the year just about to end, or the next one? My guess is that if a student is now in Yr 2 and in the school year beginning about September 1 will be in Yr 3, we will refer to him/her as being in Yr 2 until September 1, at which time we will say he is in Yr 3. Is this correct?

Regards,

Jerry

The parameter "YM" will give the months, ignoring the years.

KOENIG Yvan wrote:

Funny

Three answers exactly at same time

How did the 10:19 reply slip in ahead of the two 10:18 replies?

🙂

Yep, I posted my answer then decided I better double check instead of trusting my memory so I deleted what I wrote, checked it out, then posted it again.

I sometimes go back to check my answers too, but not often enough.

Jerru

Link is now: http://contello.net/Site/NumbersCommunity/StudentYear.numbers