DateDifference Problem 1

[DanKay1]

DateDifference

I need to calculate the difference between two dates and find out how old was the person at particular point of time. I need to find the nearest birthday and cut off point is 6 months.

Ex:
DOB: 01/01/2000
06/01/2004
Difference is 4 years and 6 months, but nearest birthday will round it to 5 years.

DOB: 01/25/2000
06/23/2004
Difference is 4 years and 5 months and about 28 days. nearest birthday should be 4 years.

My Solution that doesn't take days into consideration.
UPDATE test1 SET test1.IssueAge = Round(DateDiff('d',[DOB],[Date])/365.25)
.25 takes care of leap year.

Do you have a better solution?

Thank you guys.

 

[RickSpr]

I think in your examples you meant to use dates in July instead of June.

There's something you need to clarify, or perhaps decide, first. What is the cutoff date for a birthday of March 31st? Is it September 30th or October 1st? I assume a similar rule will apply to May 31st, August 30th and 31st (and the 29th in a non-leap year), October 31st, and December 31st.


Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[DanKay1]

RickSpr,

You raised an interesting point, I think its better to compare if the difference is greater than 183(366/2) and increment by 1 if it is.

Instead of comparing months and day of the month, its better to count total days.

What do you think?

 

[RickSpr]

What is "better" depends on your requirements. If your requirements are lenient in these gray areas, It's certainly easier to use the 183 day criterion.

I actually wrote a function that would compute this based on criteria like 'March 31st + 6 months = September 30th'. I did my best to simplify and clarify it, but it's still quite obscure. I thought about how to modify it so that 'March31st + 6 months = October 1st', but that would make it even worse. (That's when I decided to pose the question to you.) Here's that function, in case it's any use to you.

Public Function RoundedAge(AsOfDate As Date, DOB As Date) As Integer
' Tek-Tips thread 705-913621, response to DanKay1
' Calculates a person's age, rounded to the nearest year.
' For the purpose of rounding, a half year is determined by adding or
' subtracting 6 to/from the month number of the As Of date. When that
' yields an invalid date (because the day is too large for that month),
' the last day of the month begins the next half year.
    Dim datBirthday As Date  ' person's birthday in the AsOf year
    Dim datCutoff As Date    ' cutoff date in the AsOf year
    Dim intYears As Integer  ' difference in years numbers DOB to AsOfDate
    Dim intMonths As Integer
    Dim intAge As Integer
    
    ' Compute the person's age on their birthday in the AsOfDate year
    intYears = Year(AsOfDate) - Year(DOB)
    
    ' Compute the person's birthday in the AsOf year.
    datBirthday = DateAdd("yyyy", intYears, DOB)
    ' Note: If the person was born on Feb. 29th and the AsOf year is
    ' not a leap year, it comes out Feb. 28th.
    
    ' Compute the cutoff date in the AsOf year. This will be either
    ' 6 months before or 6 months after the birthday. Note that the
    ' possible error in datBirthday will be propagated to datCutoff.
    intMonths = IIf(Month(datBirthday) <= 6, 6, -6)
    datCutoff = DateAdd("m", intMonths, datBirthday)
    ' Correct the Feb. 29th error if necessary
    If Day(DOB) > 28 And Day(datCutoff) = 28 Then datCutoff = datCutoff + 1
    
    ' Compute the person's actual age on the AsOf date
    intAge = intYears
    If AsOfDate < datBirthday Then intAge = intAge - 1

    ' If the AsOf date is on or after the cutoff date, round up
    If AsOfDate >= datCutoff Then intAge = intAge + 1
    
    RoundedAge = intAge
End Function

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[DanKay1]

RickSpr,
Thank you for taking yout time to help me tackle this problem. Your solution looks good, I want to use both solutions yours and 183 cutoff period and see the variance.

Your solution is greatly appreciated.