Pivot Table and Date Ranges

[Tennisguuy]

I have a spreadsheet with various columns with losses and loss dates for each loss. The problem is the loss dates generally correspond to a policy year. However the spreadsheet doesn't include a policy year so I create a column for the policy year. The losses could range over a ten year period but most of the time its just about seven. Since the policy year can be different throughout the years or on different account I have to manually adjust my formula to show the policy year.

For example if the policy year is 5/20/2016 to 5/20/2017 my formula is general if(and(J2>42510,j2<42,875),2016). J2 is the loss date of the claim. I have the formula in Colum U2.

Here's an example of the formula I have in cell U2 to cover losses over the seven year period.

IF(AND(J2>39588,J2<39953),2008,IF(AND(J2>39953,J2<40318),2009,IF(AND(J2>40318,J2<40683),2010,IF(AND(J2>40683,J2<41049),2011,IF(AND(J2>41049,J2<41414),2012,IF(AND(J2>41414,J2<41779),2013,IF(AND(J2>41779,J2<42144),2014,IF(AND(J2>38857,J2<39222),2006,IF(AND(J2>39222,J2<39588),2007,IF(AND(J2>38492,J2<38857),2005,IF(AND(J2>42144,J2<42510),2015,IF(AND(J2>42510,J2<42875),2016,IF(AND(J2>42875,J2<43240),2017,IF(AND(J2>43240,J2<43605),2018))))))))))))))

I usually copy this formula down. Sometimes I can have 200 rows of data. I was wondering if there was an easier way to do this so that if the date change I could just change the dates by not have to manually adjust the formula each time.


For example if I list the effective date in a row of cells and have J2 feed of these date

Eff. Date Exp Date Pol. Yr
5/20/2013 5/20/2014 2013
5/20/2014 5/20/2015 2014
5/20/2015 5/20/2016 2015
5/20/2016 5/20/2017 2016
5/20/2017 5/20/2018 2017

 

[crazydragon84]

are you just creating a policy year column?

=IF(MONTH(Effective_Date)>5, YEAR(Effective_Date) + 1, IF(AND(MONTH(Effective_Date)=5,DAY(Effective_Date)>20), YEAR(Effective_Date) + 1,YEAR(Effective_Date) ))

Something like that?

 

[Tennisguuy]

Yes I am just creating a policy year column. I would have to work in the expiration because the date of loss would fall with in the effective and expiration

 

[crazydragon84]

I would have to work in the expiration because the date of loss would fall with in the effective and expiration

you just want the policy year for the claim right? so change the "effective_date" I have to cell that has the claim date (or J2).

 

[Tennisguuy]

I used this formula IF(MONTH(J3)>5,YEAR(J3)+1,IF(AND(MONTH(J3)=5,DAY(J3)>20),YEAR(J3)+1,YEAR(J3) )). The policy term is 6/1/15 to 6/1/16 and the date of loss is 11/24/15 so the policy year should be 2015 but the formula returned 2016

 

[crazydragon84]

your initial post had effective dates and years starting and ending on May 20th. so i made my formula just takes the year of whatever date you are looking at, and then adds 1 year to it if its after May 20th. if your year cut off is 5/31 then all you need is IF(MONTH(J3)>5,YEAR(J3)+1,Year(J3))

 

[Tennisguuy]

Thanks but the effective date will change depending upon the account. I was creating a template to use on multiple accounts with different effective date. I just use that one as an example. In the actual template I wanted the formula to adjust based on the dates enter because they would always be different.

 

[crazydragon84]

so have an "input date" at the top and have the formula reference that input date. then, instead of hardcoding the days and months in the below formula you reference to the input date

=IF(MONTH(Effective_Date)>5, YEAR(Effective_Date) + 1, IF(AND(MONTH(Effective_Date)=5,DAY(Effective_Date)>20), YEAR(Effective_Date) + 1,YEAR(Effective_Date) ))

e.g.:
=IF(MONTH(Effective_Date)>month(input_Date)....

 

[Tennisguuy]

Thanks so much for your help really appreciate it. That worked