Calculate age (confusion because of the result of days)

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

Calculate age (confusion because of the result of days)

Post by YasserKhalil »

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 ??

User avatar
HansV
Administrator
Posts: 78241
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

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

Post by HansV »

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.
Best wishes,
Hans

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

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

Post by YasserKhalil »

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

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

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

Post by YasserKhalil »

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 !!??

User avatar
HansV
Administrator
Posts: 78241
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

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

Post by HansV »

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.
Best wishes,
Hans

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

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

Post by YasserKhalil »

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.

User avatar
HansV
Administrator
Posts: 78241
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

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

Post by HansV »

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
Best wishes,
Hans

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

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

Post by YasserKhalil »

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.

User avatar
Leif
Administrator
Posts: 7193
Joined: 15 Jan 2010, 22:52
Location: Middle of England

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

Post by Leif »

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

User avatar
HansV
Administrator
Posts: 78241
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

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

Post by HansV »

"Ah, but I was so much older then, I'm younger than that now"

(Bob Dylan, My Back Pages)
Best wishes,
Hans