=(DateDiff("yyyy",[BirthDate],Now())) Make show yrs & 10ths

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The following formula displays in full years only:
=(DateDiff("yyyy",[BirthDate],Now())). How can it be altered to display
years and 10ths of years. AARGH.
 
You'll find that the equation won't accurately show the age. If the birthday
hasn't been reached yet in the current year, it'll still show the person one
year older. You will find a better, one line formula here:

http://www.mvps.org/access/datetime/date0001.htm

Once you have that, it is still necessary to determine age in 10ths of a
year.

If you modify the formula to

DateDiff("ww", [BirthDate], Date())+Int(Format(Date(), "mmdd") < Format(
[BirthDate], "mmdd"))

You will get weeks instead of years. Dividing by approximately 52.19 (number
of weeks in a year) and rounding to 1 decimal place will give you years and
tenths of years.
 
I tried the modified expression and got an incorrect result. A birth date of
7/30/1980 returns an age of 24.7.

Wayne Morgan said:
You'll find that the equation won't accurately show the age. If the birthday
hasn't been reached yet in the current year, it'll still show the person one
year older. You will find a better, one line formula here:

http://www.mvps.org/access/datetime/date0001.htm

Once you have that, it is still necessary to determine age in 10ths of a
year.

If you modify the formula to

DateDiff("ww", [BirthDate], Date())+Int(Format(Date(), "mmdd") < Format(
[BirthDate], "mmdd"))

You will get weeks instead of years. Dividing by approximately 52.19 (number
of weeks in a year) and rounding to 1 decimal place will give you years and
tenths of years.

--
Wayne Morgan
MS Access MVP


NewLifeAl said:
The following formula displays in full years only:
=(DateDiff("yyyy",[BirthDate],Now())). How can it be altered to display
years and 10ths of years. AARGH.
 
Try the following.

Int(10 * DateDiff("D",[Birthdate],Date())/365.25) / 10

That will be pretty accurate as long as you are looking at tenths of years.
NewLifeAl said:
I tried the modified expression and got an incorrect result. A birth date of
7/30/1980 returns an age of 24.7.

Wayne Morgan said:
You'll find that the equation won't accurately show the age. If the birthday
hasn't been reached yet in the current year, it'll still show the person one
year older. You will find a better, one line formula here:

http://www.mvps.org/access/datetime/date0001.htm

Once you have that, it is still necessary to determine age in 10ths of a
year.

If you modify the formula to

DateDiff("ww", [BirthDate], Date())+Int(Format(Date(), "mmdd") < Format(
[BirthDate], "mmdd"))

You will get weeks instead of years. Dividing by approximately 52.19 (number
of weeks in a year) and rounding to 1 decimal place will give you years and
tenths of years.

--
Wayne Morgan
MS Access MVP


NewLifeAl said:
The following formula displays in full years only:
=(DateDiff("yyyy",[BirthDate],Now())). How can it be altered to display
years and 10ths of years. AARGH.
 
Hmm, result from the Immediate window as an example, done on 3/20/2006:

?(DateDiff("ww", #7/30/1980#, Date)+Int(Format(Date,
"mmdd")<Format(#7/30/1980#, "mmdd"))) / 52.19
25.617934470205
 
Back
Top