# Thread: Age in Years Months Days

1. Hi there!

I have been struggling with putting the above on a form for someones age.

Does anyone have a formula for this? I am ok with the years and months, but keep coming unstuck on the days

I.e. Someone whose date of birth is 09/08/2004 equates to 5 yrs 5 mnths and 13 days, but where a date of birth is say 30/01/2005 my formula comes back with 4 yrs 11 mnths and #Error

It's something to do with the day of the month being later than the current day.

Any help would be grately appreciated

Cheers,

Niven

2. What is the formula you are using?

3. Originally Posted by John Hutchison
What is the formula you are using?
It's a bit of a mess really.

For years: - =IIf(DateValue((Left([dob],6) & Year(Date())))>Date(),DateDiff("yyyy",Left([dob],6) & Year([Dob])+1,Date()),DateDiff("yyyy",[dob],Date()))

For Months:- =IIf(DateValue((Left([dob],6) & Year(Date())))>Date(),IIf(DatePart("d",[dob])>DatePart("d",Date()),DateDiff("m",Left([dob],6) & Year(Date())-1,Date())-1,DateDiff("m",Left([dob],6) & Year(Date())-1,Date())),DateDiff("m",Left([dob],6) & Year(Date()),Date()))

For Days:- =IIf(Day(Date())-Day([dob])<0 And Month(Date())-Month([dob])<0,DateDiff("d",Day([dob]) & "/" & Month(Date())-1 & "/" & Year(Date()),Date()),IIf(Day(Date())-Day([dob])<0 And Month(Date())-Month([dob])>=0,DateDiff("d",Day([dob]) & "/" & Month(Date())-1 & "/" & Year(Date()),Date()),IIf(Day(Date())-Day([dob])>=0,DateDiff("d",Day([dob]) & "/" & Month(Date()) & "/" & Year(Date()),Date()))))

The years and the months works and the days is fine if the day is before the current day. i.e. 15th before 22nd

Cheers

Niven

4. I thought it might be easier to stat with what you already, rather than from scratch.

Is dob actually a Text field rather than a Date? You are using Left([dob],6) for instance.

If it is text what format is it in?

5. Originally Posted by John Hutchison
I thought it might be easier to stat with what you already, rather than from scratch.

Is dob actually a Text field rather than a Date? You are using Left([dob],6) for instance.

If it is text what format is it in?
It is actually a date field on the table, but being interrogated in a text field on the form.

6. Here is a sub from The Access web that does the job without using all the IIF statements seem to come up normally.

Code:
```Public Sub CalcAge(vDate1 As Date, vdate2 As Date, ByRef vYears As Integer,
ByRef vMonths As Integer, ByRef vDays As Integer)
' Comments : calculates the age in Years, Months and Days
' Parameters:
' vDate1 - D.O.B.
' vDate2 - Date to calculate age based on
' vYears - will hold the Years difference
' vMonths - will hold the Months difference
' vDays - will hold the Days difference
vMonths = DateDiff("m", vDate1, vdate2)
vDays = DateDiff("d", DateAdd("m", vMonths, vDate1), vdate2)
If vDays < 0 Then
' wierd way that DateDiff works, fix it here
vMonths = vMonths - 1
vDays = DateDiff("d", DateAdd("m", vMonths, vDate1), vdate2)
End If
vYears = vMonths \ 12 ' integer division
vMonths = vMonths Mod 12 ' only want leftover less than one year
End Sub```
This function would be appropriate where you wanted to display Age on a form that showed one person at a time.

You would need to declare three variables vYears, vMonths and vDays.

Then in the On Current event of the form and the After Update event for DOB, run procedure passing it DOB and Date(), then set the three text boxes to the values of the three variables.

7. Originally Posted by John Hutchison
Here is a sub from The Access web that does the job without using all the IIF statements seem to come up normally.

Code:
```Public Sub CalcAge(vDate1 As Date, vdate2 As Date, ByRef vYears As Integer,
ByRef vMonths As Integer, ByRef vDays As Integer)
' Comments : calculates the age in Years, Months and Days
' Parameters:
' vDate1 - D.O.B.
' vDate2 - Date to calculate age based on
' vYears - will hold the Years difference
' vMonths - will hold the Months difference
' vDays - will hold the Days difference
vMonths = DateDiff("m", vDate1, vdate2)
vDays = DateDiff("d", DateAdd("m", vMonths, vDate1), vdate2)

If vDays < 0 Then
' wierd way that DateDiff works, fix it here
vMonths = vMonths - 1
vDays = DateDiff("d", DateAdd("m", vMonths, vDate1), vdate2)
End If
vYears = vMonths \ 12 ' integer division
vMonths = vMonths Mod 12 ' only want leftover less than one year
End Sub```
This function would be appropriate where you wanted to display Age on a form that showed one person at a time.

You would need to declare three variables vYears, vMonths and vDays.

Then in the On Current event of the form and the After Update event for DOB, run procedure passing it DOB and Date(), then set the three text boxes to the values of the three variables.
John,

Many thanks for this. I'll try it out and let you know

Cheers,

Niven

8. This is an Excel spreadsheet I use for a Birthday list which does give the correct result for year, months, days.

Perhaps a similar format or query may work in Access or not.

9. The Excel example simplifies things by only working it out for 31/12/2010. This means it can completely bypass the complicating issues:
• Has the person had their birthday yet this year? and
• Have they passed the day of their birthday in the current month?

10. This morning while looking for something else I came across this post by Slinky (aka Hans) that has the following expressions that could be put into a query.

Years: Year([Date2])-Year([Date1])+(Month([Date2])<Month([Date1]) Or Month([Date2])=Month([Date1]) And Day([Date2])<Day([Date1]))

Months: (Month([Date2])-Month([Date1])+(Day([Date2])<Day([Date1]))+12) Mod 12

Days: DateDiff("d",DateSerial(Year([Date2]),Month([Date2])+(Day([Date2])<Day([Date1])),Day([Date1])),[Date2])

11. Originally Posted by Niven
John,

Many thanks for this. I'll try it out and let you know

Cheers,

Niven
John,

Have tried this out and it works a treat!

Many thanks for your time on this. Your later post should be quite usesfule too!

Cheers,

Niven

12. Originally Posted by John Hutchison
This morning while looking for something else I came across this post by Slinky (aka Hans) that has the following expressions that could be put into a query.

Years: Year([Date2])-Year([Date1])+(Month([Date2])<Month([Date1]) Or Month([Date2])=Month([Date1]) And Day([Date2])<Day([Date1]))

Months: (Month([Date2])-Month([Date1])+(Day([Date2])<Day([Date1]))+12) Mod 12

Days: DateDiff("d",DateSerial(Year([Date2]),Month([Date2])+(Day([Date2])<Day([Date1])),Day([Date1])),[Date2])
John,

This works a treat as well!

Once again, many thanks for your time on this.
Cheers,

Niven

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•