Spreadsheet: 268432d1380635425-if-function-tied-to-dates-claims-made-yr-example
Sheet: Sheet1

 |  |  |  | inception | expiration | inception | expiration
 | key off of | should be | retro dt | 2014-01-01 00:00:00 | 2015-01-01 00:00:00 | 2014-01-01 00:00:00 | 2015-01-01 00:00:00 | match months? | match days?
prior to | 2014-06-30 00:00:00 | 1 | 2014-01-10 00:00:00 |  |  |  |  |  |  |  | Notes:
after | 2014-07-01 00:00:00 | 1 | 2014-07-20 00:00:00 |  |  |  |  |  |  |  | insurance rating program
prior to | 2013-06-30 00:00:00 | 2 | 2013-01-10 00:00:00 |  |  |  |  |  |  |  | inception is the date the insurance begins
after | 2013-07-01 00:00:00 | 1 | 2013-07-20 00:00:00 |  |  |  |  |  |  |  | expiration is the end of the ins pd.
prior to | 2012-06-30 00:00:00 | 3 | 2012-01-10 00:00:00 |  |  |  |  |  |  |  | col C - should be is what the factor
after | 2012-07-01 00:00:00 | 2 | 2012-07-20 00:00:00 |  |  |  |  |  |  |  | should be for the calculation
prior to | 2011-06-30 00:00:00 | 4 | 2011-01-10 00:00:00 |  |  |  |  |  |  |  | pblm
after | 2011-07-01 00:00:00 | 3 | 2011-07-20 00:00:00 |  |  |  |  |  |  |  | match days appears to match until I change the values in cells
prior to | 2010-06-30 00:00:00 | 5 | 2010-01-10 00:00:00 |  |  |  |  |  |  |  | D39 - D50
after | 2010-07-01 00:00:00 | 4 | 2010-07-20 00:00:00 |  |  |  |  |  |  |  | for example, change D41 from 1/31 to 2/27 and the days match no longer matches
prior to | 2009-06-30 00:00:00 | 5 | 2009-01-10 00:00:00
after | 2010-07-01 00:00:00 | 5 | 2009-07-20 00:00:00 |  |  |  |  |  |  |  | So, if the retro date is in the first 6 months of the prior year they get a 1.
prior to | 2014-06-30 00:00:00 | 1 | 2014-01-01 00:00:00 |  |  |  |  |  |  |  | If they are in the last half of the year prior to the insurance period they get a 1.
after | 2014-07-01 00:00:00 | 1 | 2014-07-01 00:00:00 |  |  |  |  |  |  |  | If they are in the first half of the year in the previous year they get a 2
prior to | 2013-06-30 00:00:00 | 2 | 2013-01-01 00:00:00 |  |  |  |  |  |  |  | If they are in the last half of the year in the previous year they get a 1
after | 2013-07-01 00:00:00 | 1 | 2013-07-01 00:00:00
prior to | 2012-06-30 00:00:00 | 3 | 2012-01-01 00:00:00
after | 2012-07-01 00:00:00 | 2 | 2012-07-01 00:00:00
prior to | 2011-06-30 00:00:00 | 4 | 2011-01-01 00:00:00
after | 2011-07-01 00:00:00 | 3 | 2011-07-01 00:00:00
prior to | 2010-06-30 00:00:00 | 5 | 2010-01-01 00:00:00
after | 2010-07-01 00:00:00 | 4 | 2010-07-01 00:00:00
prior to | 2009-06-30 00:00:00 | 5 | 2009-01-01 00:00:00
after | 2010-07-01 00:00:00 | 5 | 2009-07-01 00:00:00
prior to | 2014-06-30 00:00:00 | 1 | 2014-01-20 00:00:00
after | 2014-07-01 00:00:00 | 1 | 2014-07-25 00:00:00
prior to | 2013-06-30 00:00:00 | 2 | 2013-01-20 00:00:00
after | 2013-07-01 00:00:00 | 1 | 2013-07-25 00:00:00
prior to | 2012-06-30 00:00:00 | 3 | 2012-01-20 00:00:00
after | 2012-07-01 00:00:00 | 2 | 2012-07-25 00:00:00
prior to | 2011-06-30 00:00:00 | 4 | 2011-01-20 00:00:00
after | 2011-07-01 00:00:00 | 3 | 2011-07-25 00:00:00
prior to | 2010-06-30 00:00:00 | 5 | 2010-01-20 00:00:00
after | 2010-07-01 00:00:00 | 4 | 2010-07-25 00:00:00
prior to | 2009-06-30 00:00:00 | 5 | 2009-01-20 00:00:00
after | 2010-07-01 00:00:00 | 5 | 2009-07-25 00:00:00
prior to | 2014-06-30 00:00:00 | 1 | 2014-02-28 00:00:00
after | 2014-07-01 00:00:00 | 1 | 2014-11-01 00:00:00
prior to | 2013-06-30 00:00:00 | 2 | 2013-02-27 00:00:00
after | 2013-07-01 00:00:00 | 1 | 2013-07-31 00:00:00
prior to | 2012-06-30 00:00:00 | 3 | 2012-01-31 00:00:00
after | 2012-07-01 00:00:00 | 2 | 2012-07-31 00:00:00
prior to | 2011-06-30 00:00:00 | 4 | 2011-01-31 00:00:00
after | 2011-07-01 00:00:00 | 3 | 2011-07-31 00:00:00
prior to | 2010-06-30 00:00:00 | 5 | 2010-01-31 00:00:00
after | 2010-07-01 00:00:00 | 4 | 2010-07-31 00:00:00
prior to | 2009-06-30 00:00:00 | 5 | 2009-01-31 00:00:00
after | 2010-07-01 00:00:00 | 5 | 2009-07-31 00:00:00