## Calculate age (confusion because of the result of days)

YasserKhalil
PlatinumLounger
Posts: 4822
Joined: 31 Aug 2016, 09:02

### Calculate age (confusion because of the result of days)

Hello everyone

I have a UDF (It's for Mr. Hans) that calculates the age

Code: Select all

``````Function CalculateAge(birth, start, txt As String)
Dim y As Long, m As Long, d As Long
If Not IsDate(birth) Or Not IsDate(start) Then GoTo Skipper
m = DateDiff("m", birth, start)
d = DateDiff("d", DateAdd("m", m, birth), start)
If d < 0 Then
m = m - 1
d = DateDiff("d", DateAdd("m", m, birth), start)
End If
y = m \ 12
m = m Mod 12
Select Case txt
Case "d"
CalculateAge = d
Case "m"
CalculateAge = m
Case "y"
CalculateAge = y
End Select
Exit Function
Skipper:
CalculateAge = ""
End Function``````
The UDF works fine as for the years and months, but as for the days I am confused a little
For example: the bof 31/12/2009 ... and the start date is 1/10/2021 (First of October)
The result of the udf is 11-09-01

while when using formulas like that

Code: Select all

``=IF(A2="","",IF(A2>1,DATEDIF(A2,Start_Date,"md"),""))``
I got a different result as for the day
11-09-00

Which is correct in that case ?? HansV
Posts: 76649
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

### Re: Calculate age (confusion because of the result of days)

The DATEDIF function with "md" as third argument is not reliable - it can produce incorrect results, as in this example.
See the official documentation:
Known issues

The "MD" argument may result in a negative number, a zero, or an inaccurate result.
Regards,
Hans

YasserKhalil
PlatinumLounger
Posts: 4822
Joined: 31 Aug 2016, 09:02

### Re: Calculate age (confusion because of the result of days)

Thanks a lot. So the correct result here is 1 not 0?

YasserKhalil
PlatinumLounger
Posts: 4822
Joined: 31 Aug 2016, 09:02

### Re: Calculate age (confusion because of the result of days)

When we calculate it manaually. we say 1 minus 31 and in that case we take 30 days from the month
So 30 + 1 = 31 >> 31 -31 =0

Or when we take days from month we take 31 days !!?? HansV
Posts: 76649
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

### Re: Calculate age (confusion because of the result of days)

This is a very tricky subject, because not all months have the same number of days. If you search the internet, there are endless discussions about it.

Many people argue that it is always 1 month and 0 days from the last day of a month to the last day of the next month.

So from 30 November 2021 to 31 December 2021 would be 1 month and 0 days.
But then we run into trouble, because it is also reasonable to say that it is 1 month and 0 days from 30 November 2021 to 30 December 2021, since both are the 30th day of the month.
If we follow that, we'd have to say that it is 1 month and 1 day from 30 November 2021 to 31 December 2021.
It's even worse from February to March.

Going from a 31-day month to a 30-day month is also problematic:
From 31 May 2022 to 30 June 2022 could be 1 month and 0 days, since both dates are the last day of their month.
Most people would agree that it is 1 month and 1 day from 31 May 2022 to 1 July 2022. That looks consistent: 1 July is 1 day after 30 June, so it is 1 day more than 1 month and 0 days.

But one could also argue that since 30 is less than 31, it is 0 months and 30 days from 31 May 2022 to 30 June 2022.
However, that would mean that there is no date that is exactly 1 month and 0 days after 31 May 2022.

Whichever solution you prefer, there will always be inconsistencies.
Regards,
Hans

YasserKhalil
PlatinumLounger
Posts: 4822
Joined: 31 Aug 2016, 09:02

### Re: Calculate age (confusion because of the result of days)

Thank you very much for this detailed reply.
How can I modify the UDF to get 0 for days as the existing UDF returns 1 in that example ..?
Just notify me of the part that I need to modify. HansV
Posts: 76649
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

### Re: Calculate age (confusion because of the result of days)

I cannot imagine any scenario in which it is 0 days from the 31st of a month to the 1st of another month. That makes no sense.

But I assume that you will insist. If so, simply use DATEDIF in the UDF:

Code: Select all

``````Function CalculateAge(birth, start, txt As String)
If Not IsDate(birth) Or Not IsDate(start) Then GoTo Skipper
Select Case txt
Case "d"
CalculateAge = Evaluate("DATEDIF(" & CLng(birth) & "," & CLng(start) & ",""md"")")
Case "m"
CalculateAge = Evaluate("DATEDIF(" & CLng(birth) & "," & CLng(start) & ",""ym"")")
Case "y"
CalculateAge = Evaluate("DATEDIF(" & CLng(birth) & "," & CLng(start) & ",""y"")")
End Select
Exit Function
Skipper:
CalculateAge = ""
End Function``````
Regards,
Hans

YasserKhalil
PlatinumLounger
Posts: 4822
Joined: 31 Aug 2016, 09:02

### Re: Calculate age (confusion because of the result of days)

Thank you very much, my tutor. It is just a matter of confusion and I couldn't decide which result is correct and you assured that there are still discussions and no one can prove a final and correct attitude. Leif
Posts: 7091
Joined: 15 Jan 2010, 22:52
Location: Middle of England

### Re: Calculate age (confusion because of the result of days)

And it gets even more complicated if you are trying to determine the age of someone in Korea Leif HansV