I want to categorise cases depending on the number of days between two dates:
If [DOB] is before [Visit Date], I want to return “P”
If [Visit Date] is the same day as or up to a year later than [DOB], I want to return “I”
If [Visit Date] is longer than a year after [DOB], I want to return “T”
Currently, I am using the following formula to achieve this:
=If(DaysBetween([DOB];[Visit Date])<0) Then “P” ElseIf(DaysBetween([DOB];[Visit Date])<365) Then “I” Else “T”
This works fine most of the time but doesn’t take account of leap years. For example, if it is a leap year and [DOB]=16th June 2015 and [Visit Date]=15th June 2016, the formula returns “T” when it should return “I”.
Could you please help with any way to take leap years into account? Unfortunately, I don’t have access to the universe so any solution would need to be done in Webi.
It must be exceedingly rare for that to happen, surely?
One way I can see is to complicate your if statement, and if the period spans a Feb29th in 2004,2008,2012 . . . . then perform a slightly different calculation.
Perhaps it would be easier to add (manually) one year to the [DOB], and compare against that - you don’t really need the number of days.
You could try breaking down DoB into day, month and year, adding 1 to year and rebuilding to a date which is DoB + 1 year. (You’ll need to use format number to preserve any leading 0s in day and month.) Then compare the visit date to DoB + 1 yr.
RelativeDate won’t work because it works on +/- number of days so you’ll run into the leap year problem again…
Thanks for your very helpful replies. I did as you suggested and added a year to DoB by breaking down and rebuilding the date, which now works well.
Mikeil, it is quite rare but, since a visit is expected around a year after DoB, there is the potential for a fair few cases to happen each leap year.
If anyone wants to do a similar thing but to include a test for a leap year in an if-statement, you may find this algorithm for leap years helpful: http://en.wikipedia.org/wiki/Leap_year#Algorithm
Thinking about it, a less cumbersome method would be to use relativedate to add 365 to DoB, then compare the day of DoB and DoB+365; if they match, then use 365 to get DoB+1year, otherwise use 366.
So your DoB+1 year calculation would look something like this:
(BOB member since 2009-01-09)
(BOB member since 2002-08-29)