Monday, April 09, 2012

Anniversary - Encore - A Giant Leap

Welcome to another - and hopefully final - article on the "Birthday Problem".

As Oli points out, you cannot really calculate the age by assuming that a year has 365 days. The "vanilla" Age field in the Contact business component does exactly that, and this is wrong. Naturally, because we earthlings need an extra day every four years to complete a full circle around the sun.

Now the question is, how can we create a more accurate Age field?

While digging through the web, I found two approaches. One is to calculate the number of leap years between a person's birth day and the current day. Let's call this the "Leap Year Approach". I didn't find a Siebel implementation of that one, so I created my own.

The other approach is tributed to Ji Yong Dijkhuis and is published on it.toolbox.com. Ji Yong's approach is also very interesting and I call it the "Exact Birthday Approach".

I decided to present both approaches and wait for comments.

1. The Leap Year Approach

The center of this solution is to find the number of leap years between a person's birth date and the current day. To do this, you use the following equation:

[Number of leap years from 0 to today] - [Number of leap years from 0 to year of birth]

The number of leap years can be found by dividing the year number by four. Because of the fact that some years (like 1900) are not leap years, we narrow our validity frame to birth dates between 1900 and 2100, so we have a proper Y2.1K problem here ;-).

The equation translates into Siebel Query Language as follows:

((JulianYear(Today())-4713)/4)-((JulianYear([Birth Date])-4713)/4)

Remember, the JulianYear() function returns the number of years elapsed since 4713 B.C.

Now we have the number of leap years since a person has been born. Thus we can shift the birth date one day per leap year towards today, so that the division by 365 is valid again. The final calculated value is:

(Today()-([Birth Date]+(((JulianYear(Today())-4713)/4)-((JulianYear([Birth Date])-4713)/4))))/365

2. The Exact Birthday Approach

Ji Yong's approach is more elegant while still a bit unconventional:

The following formula uses the JulianYear function as well and subtracts 1 from the difference between a person's birth year and the current year if the birthday is today or in the past.

IIf([Birth Date]= "", "", IIf(ToChar(Today(),"MMDD") >= ToChar([Birth Date], "MMDD"), JulianYear(Today()) - JulianYear([Birth Date]), (JulianYear(Today()) - JulianYear([Birth Date]) - 1)))

As you can see, the evaluation whether the birthday is today or in the past is done via a comparison of the month and day as a string (MMDD format mask).

Both approaches seem to be valid and allow us to calculate the age of a person exactly.

click to enlarge
The screenshot above shows a test with the vanilla Age field, a custom field which uses the JulianYear function and the AHA Age field. The AHA Age field is using approach 1 and is the only one which correctly displays the age on April 2nd for a person who has birthday on April 3rd.

have a nice day

@lex

No comments: