Calculating Customer's 1/2 Birthday (not age) STILL UNRESOLVED

[poochbeast]

....sorry if this appears more than once...

BIG, BIG thanks to John, Van, and especially Tom. You guys are simply
amazing!

Unfortunately, none of these formula's is exactly what i'm looking
for. Perhaps it's my fault...maybe I didn't state my case clearly
enough. If you would be so kind as to give it another try, it would be
MOST appreciated.

As I mentioned, insurance premiums are based on your NEAREST birthday.
I have the client's birthdate [BIRTHDATE], but I'm trying to create a
2nd field labeled "Nearest Age Change." This will ALWAYS be the
client's nearest HALF birthday. This is when the insurance company
views his age as actually changing. I'm trying to give myself a way to
determine at a glance if he has an age change coming up soon, or if he
recently already past one.

Here are some examples. All assume today's date to be 3/25/2005:

Client's Bday is 5/16/1960. His nearest age change was 11/17/2004.
Client's Bday is 11/17/1960. His nearest age change will be 5/18/2005.
Client's Bday is 8/17/1936. His nearest age change was 2/18/2005.
Client's Bday is 10/29/1929. His nearest age change will be 4/30/2005.
Client's Bday is 3/24/1970. His nearest age change was 9/25/2004.

I hope this clarifies thing a bit. I'm counting on you guys!
Thanks in advance.

Paul

 

[Douglas J. Steele]

Can you not figure out the birthday that's passed and the birthday that's
yet to come and use DateDiff to figure out the number of days between today
and those two dates and pick the one that has the smaller difference?

 

[Paul Overway]

This works...although I'm not sure why you add an extra day.

Function InsuranceBday(RealBday As Date, Optional TheDate As Date = 0) As
Date

Dim dtmNext As Date
Dim dtmPast As Date

If TheDate = 0 Then TheDate = Date

dtmNext = DateSerial(Year(TheDate), Month(RealBday) + 6, Day(RealBday))
+ 1
dtmPast = DateSerial(Year(TheDate) - 1, Month(RealBday) + 6,
Day(RealBday)) + 1

If Abs(TheDate - dtmNext) <= Abs(TheDate - dtmPast) Then
InsuranceBday = dtmNext
Else
InsuranceBday = dtmPast
End If

End Function

 

[Van T. Dinh]

See my suggestion in the original thread.

I agree with Paul about the extra day. In my code, I added an extra day to
match your sample. However, if you don't want the extra day, you can easily
modify the expression.