I Work in the insurance world and have a problem dealing with Feb 29.. Not just a leap year but the actual date of feb29. My current formula says...
IF (datediff('day',pol2exp,pol2eff)and<365) and (datediff('day',polteff,originalnewdate)and<365) THEN New ELSE Renewal END
SO the formula works 99.9% of the time. Here is what the formula is actually doing. The first datediff looks at last years policy and says am I less than 365. A policy is typically exactly 365. For example, 1/1/2015 - 1/1/2016 or 3/1/2015 - 3/1/2016. A policy can start on any date and end on any date, but sometimes a policy is 6 months. The first 12 months of a policy is new and any transaction after 12 months is renewal.
Here is an example of the issue.. Feb 29, 2012 is a leap year date.
The first date is poleffdate and the second date is polexpdate
Policy A - 9/1/2011 - 3/1/2012 a six month policy
Policy B - 3/1/2012 - 9/1/2012 another six month policy.
Policy A passes the first test of being less than 365 days, but policy B will fail because I will bill the last day of that policy. I will issue the final bill on 9/1/2012 (polteff). Polteff is 366 days away from Policy A so it falls into the renewal category and s/b new.
Policy A - 3/1/2012 - 9/1/2012 a six month policy
Policy B - 9/1/2012 - 3/1/2013 another six month policy.
Policy A passes the first test of being less than 365 days, and policy B will pass because it is less the 365 from one. I will issue the final bill on 9/1/2013 (polteff). Polteff is 365 days away from Policy A so it falls into the New category where it belongs.
It is the day of February 29th and not just the leap year. When both policies cross Feb 29, I'll get 366 on the second policy and it falls into renewal and not new.
SO I need something that works with the formula above where February 29th is between poleffdate and polexpdate of policy A. and February 29th is between poleffdate and polexpdate of policy B. then the datediff('day',polteff,originalnewdate) can be 366
otherwise my original formula will work.