Years and Months age calculation?

[Poida3934]

I need a solution to calculating a pre school students age in years
AND months as at start of school year. I've found a formula below
that works out the age in years, but can't quite nut out how to do
the months. I dont care if I display it as a second calculated field,
or if someone can devise a whizz bang formula to combine the two.
e.g. "5 Years and 4 Months"
This is what I currently have to get the years only.....
=DateDiff("yyyy",[StudDOB],[Year_Start])+Int(Format([Year_Start],"mmdd")<Format([StudDOB],"mmdd"))

Thanks in anticipation.

 

[Bob Quintal]

I need a solution to calculating a pre school students age in
years AND months as at start of school year. I've found a formula
below that works out the age in years, but can't quite nut out how
to do the months. I dont care if I display it as a second
calculated field, or if someone can devise a whizz bang formula to
combine the two. e.g. "5 Years and 4 Months"
This is what I currently have to get the years only.....
=DateDiff("yyyy",[StudDOB],[Year_Start])+Int(Format ([Year_Start],"m
mdd")<Format([StudDOB],"mmdd"))

Thanks in anticipation.

You can get the age in months by changing the "yyyy" to "m", then
use the math operators \ (integer result of division) to extract
years and mod (remainder of division) to get the months. You may
want to adjust the [year_start] setting to dateserial
([year_start],month([year_start]),1) - change the day to what you
need,- for more consistent results


=DateDiff("m",[StudDOB],dateserial([Year_Start],month
([year_start]),1)) \ 12

=DateDiff("m",[StudDOB],dateserial([Year_Start],month
([year_start]),1)) mod 12

You can also combine the two into a single long expression

 

[fredg]

I need a solution to calculating a pre school students age in years
AND months as at start of school year. I've found a formula below
that works out the age in years, but can't quite nut out how to do
the months. I dont care if I display it as a second calculated field,
or if someone can devise a whizz bang formula to combine the two.
e.g. "5 Years and 4 Months"
This is what I currently have to get the years only.....
=DateDiff("yyyy",[StudDOB],[Year_Start])+Int(Format([Year_Start],"mmdd")<Format([StudDOB],"mmdd"))

Thanks in anticipation.

Check "A More Complete DateDiff Function" at
http://www.accessmvp.com/djsteele/Diff2Dates.html