Inspecting dates of a Policy period

[kevvukeka]

Hi All,

I need a help with a macro of mine. I have attached my requirement in the excel form.

The scenario is as below.

I have a huge data of different persons and their policy periods. I need to inspect dates of each person and his policy periods. If they are in continuation I need to give on start date and one end date. for e.g.

Start date End date

Shailender 01-07-2011 30-06-2012
Shailender 01-07-2012 30-06-2013
Shailender 01-07-2013 30-06-2014

now if the data of a person is like above I would add a new column as below:

Start date End date New Start date New End date

Shailender 01-07-2011 30-06-2012 01-07-2011 30-06-2014
Shailender 01-07-2012 30-06-2013 01-07-2011 30-06-2014
Shailender 01-07-2013 30-06-2014 01-07-2011 30-06-2014

since all the dates are in continuation. I wrote a macro for that. But unable to write for the below scenario.


Start date End date

Praveen 01-07-2011 31-10-2011
Praveen 01-01-2013 30-06-2013
Praveen 01-07-2013 30-06-2014


Now for the above person I need to add column with following dates, since the dates are not following each other except last two.


Start date End date New Start date New End date

Praveen 01-07-2011 31-10-2011 01-07-2011 31-10-2011
Praveen 01-01-2013 30-06-2013 01-01-2013 30-06-2014
Praveen 01-07-2013 30-06-2014 01-01-2013 30-06-2014


I am not able to write any code for it. With my little knowledge of arrays I tried something it gives wrong values. Kindly help me finding a solution for this.


I am attaching the sample file too

Thanks for your help.

[Tommy]

How do you determine the dates to add? It appears as if you are duplicating the start date for the new start date and then I really have no clue how you are determining the new end date. Are you just taking the old end date and adding a year?

[kevvukeka]

Hi Tommy,

Thanks for the reply. Basically I need to check end date of each person and next start date. if the dates are in continuation then the start date of first row and end date date of next row should become the start date and end date of both rows. for eg.

	Start Date	End date
Shailender	01-07-2011	30-06-2012
Shailender	01-07-2012	30-06-2013

In the above table, the end date of first row and start date of next row are continuation.i.e gap of 1 day. It means the person has renewed his policy so now I need to give new start date and end date for this person. as there is continuation. The start date of first row and end date of next row will be applied to both rows. so I will update as below.

	Start Date	End date	Nw StrtDate	Nw Enddate
Shailender	01-07-2011	30-06-2012	01-07-2011	30-06-2013
Shailender	01-07-2012	30-06-2013	01-07-2011	30-06-2013

now lets suppose same person has renewed the policy exactly on end date. so the new table will look like this.

	Start Date	End date
Shailender	01-07-2011	30-06-2012
Shailender	01-07-2012	30-06-2013
Shailender	01-07-2013	30-06-2014

Now the new row is continuation i.e gap of 1 day(end date of 2nd row - start date of 3rd row). as his 3 policies are in continuation to each other. I need to given new start date. which will be the start date of 1st row and end date of last row.

Basically if the policy is in continuation, I will give one start date(the beginning one) and one end date(the last end date) as his coverage date. so the above table will look like below now.

	Start Date	End date	Nw StrtDate	Nw Enddate
Shailender	01-07-2011	30-06-2012	01-07-2011	30-06-2014
Shailender	01-07-2012	30-06-2013	01-07-2011	30-06-2014
Shailender	01-07-2013	30-06-2014	01-07-2011	30-06-2014

scenario2.

Lets suppose the same person failed to renew the policy on last day. ie gap is not one day. it may look like below.

	Start Date	End date
Shailender	01-07-2011	30-06-2012
Shailender	01-01-2013	30-06-2013
Shailender	01-07-2013	30-06-2014

In the above case he failed to renew on last day. for the first time. but for the next time he renewed it on time. so his new dates will look like below.

	Start Date	End date	Nw strtdate	Nw Enddate
Shailender	01-07-2011	30-06-2012	01-07-2011	30-06-2012
Shailender	01-01-2013	30-06-2013	01-01-2013	30-06-2014
Shailender	01-07-2013	30-06-2014	01-01-2013	30-06-2014

As you can see the dates for first row remain same as there is no continuation. But the as the 2nd row and 3rd row are in continuation, the dates are modified according.

I have policy dates of around 50,000 people with each person having minimum 4-5 rows of dates specific to him.I need to update the "Nw strtdate" and "Nw Enddate" of all those people


I apologize for this lengthy explanation, but I hope I explained the requirement properly.

Thank you..

[p45cal]

Could you supply a sheet with the starting data, names changed of course, and I'll put something together. (I suspect that there'll be lots of different names in one list and they might not be sorted - it doesn't matter, I just need to have a better idea of what the real data might look like.)

[Tommy]

Hi kevvukeka,
I think I am understanding now. The hardest part so far is the non USA date format, but that is ok, I have my head wrapped around that now.

From what I am understanding is you want the start date to be reflected in all instance of the insured person. If this person has let the policy lapse, then this now becomes the anniversary start date.
All end dates end on 6-30 so I would just add a year to the last one.
Questions:
What happens when the person renews the policy on 7-1 like they are supposed to? Does this mean they are just continuous?

As p45cal has asked please post some samples. We can make it up but I can almost guarantee that it will not be the same as your data.
Please remove all personal data.

[SamT]

kevvukeka,

As others have said, we must see the actual worksheet that you are using. Only the top few rows, with sample dates, fake names, and fake, but realistic, policy numbers.

This person is thinking that continuous coverage keeps the same policy number but non-continuous coverage gets different policy numbers. This could be of great help to write a good macro.

[kevvukeka]

Hi All,

Thanks a lot for your replies. I believe I confused you a little. I am attaching a sample file. Kindly watch it at 172% zoom so that it will give a clear idea.

In the attached sample file. First name is "John" . This person has 3 rows of policy start date and end date. If you look at the dates, only the second two rows are in continuation. (Continuation here means gap of 1 day only). So in the "New Start date" and "New End Date" column for the first row of "John" I will use the same "Start Date" and "End Date". But the 2nd row and 3rd are in continuation. So I will use the "Start Date" of 2nd row and "End date" of 3rd row as "New Start Date" and "New End Date".


Now lets look at 2nd person here. "Peter".

For "Peter" also there are 3 rows of Policy "Start Date" and "End Date". If you look at the dates, all the dates are in continuation. i.e gap of only 1 day. So for these 3 rows I will use the start date of first row(B6) of "Peter" as "New Start date" and "End date" of last row(C8) of "Peter" as "New End date".

Now lets look at 2nd person here. "Sam".

For "Sam" also there are 3 rows of Policy "Start Date" and "End Date". If you look at the dates, no date is continued into second row. so basically the person didn't renewed on the last date and gap is more than 1 day. So in this case I don't do anything but show the "Start Date" as "New Start Date" and "End Date" as "New End Date" for each row as it is.



I hope I have explained it properly, if not let me know, will do my best again. Few things to note are:

• The date format is DD/MM/YYYY
• Its not a must that each person will have only 3 rows, it may go up to 5.
• There is no fixed date for a policy to begin, it can begin any time of the year and not every coverage should be of 12 months.

I wrote a macro for the this. I first took unique names in another columns(Col F) then used "find" to search in Column (A) each and took a count how many times the name is repeated. This gives me count of number of rows each person will have. but my macro fails when the dates are not continued.


Kindly help if this can be done.

[p45cal]

I'm fairly sure I understood what you wanted from msg#3.
The following macro works in your latest sample file and places the dates in columns G:H so that they can be compared to your expected values in columns D:E.
They're all the same except where you've made a mistake in D12 and where some dates in those columns contain a time element.

This macro is unlikely to work on real data; I've used your blank rows to delineate different people and assumed each persons data is already sorted on column B or C.
I'm not proud of this macro - I put it together in haste - it would need a lot of tweaking to make it more efficient, perhaps (if there are thousands of rows) doing ALL the processing in memory and only writing once to the sheet.
I wasn't going to invest serious time in doing this since I feel your file is not anywhere near true-to-life.

However, it does contain a methodolgy for doing what you want and maybe that's all you need.

If you want more, then review the requests in msgs #4,5 and 6.

Sub blah()
With ActiveSheet
  Set DataArea = Intersect(.UsedRange, .Range("A2:C" & .Rows.Count))
  For Each are In DataArea.Columns(1).SpecialCells(2).Areas
    lastRowOfBlock = are.Row + are.Rows.Count - 1
    Set BlockStart = are.Cells(1)
    Set BlockEnd = are.Cells(1)
    If are.Rows.Count > 1 Then  'several rows in the block:
      For Each cll In are.Resize(are.Rows.Count - 1).Cells
        If cll.Offset(1, 1).Value - cll.Offset(, 2).Value > 1 Then
          'end of contiguity:
          Set BlockEnd = cll
          'place values on the sheet:
          Range(BlockStart, BlockEnd).Offset(, 6).Value = Application.Min(Range(BlockStart, BlockEnd).Offset(, 1))
          Range(BlockStart, BlockEnd).Offset(, 7).Value = Application.Max(Range(BlockStart, BlockEnd).Offset(, 2))
          'set for next block:
          Set BlockStart = cll.Offset(1)
          Set BlockEnd = cll.Offset(1)
          If cll.Row + 1 >= lastRowOfBlock Then  'it's the last row of the block:
            'place values on the sheet:
            Range(BlockStart, BlockEnd).Offset(, 6).Value = Application.Min(Range(BlockStart, BlockEnd).Offset(, 1))
            Range(BlockStart, BlockEnd).Offset(, 7).Value = Application.Max(Range(BlockStart, BlockEnd).Offset(, 2))
          End If
        Else
          'contiguous:
          Set BlockEnd = cll.Offset(1)
          If cll.Row + 1 >= lastRowOfBlock Then  'it's the last row of the block:
            'place values on the sheet:
            Range(BlockStart, BlockEnd).Offset(, 6).Value = Application.Min(Range(BlockStart, BlockEnd).Offset(, 1))
            Range(BlockStart, BlockEnd).Offset(, 7).Value = Application.Max(Range(BlockStart, BlockEnd).Offset(, 2))
          End If
        End If
      Next cll
    Else  'only one row in the block:
      'place values on the sheet:
      Range(BlockStart, BlockEnd).Offset(, 6).Value = Application.Min(Range(BlockStart, BlockEnd).Offset(, 1))
      Range(BlockStart, BlockEnd).Offset(, 7).Value = Application.Max(Range(BlockStart, BlockEnd).Offset(, 2))
    End If
  Next are
End With
End Sub

[SamT]

As others have said, we must see the actual worksheet that you are using. Only the top few rows, with sample dates, fake names, and fake, but realistic, policy numbers.

[Tommy]

I think I would get excel to copy to another sheet, add a column of numbers so we can keep the same order, sort the sheet first on name second on anniversary date. Pick up the data in an array plus 2 columns and add the dates as required.

This is all I can offer, I just can't guess as good as p45cal

Of course all of this is no good without some data to crunch. I may need to massage the data before processing (see above), so I need to see what I am actually working with.

So what I am saying is "As long as I do not have a file with the inputted data that the macro is going to work on, I will no longer participate in this request for help."

[kevvukeka]

Hi p45cal,

Thanks for that code. I will check if it works on my actual data.

Hi All,

I am attaching the real sample data. The actual data exactly looks like this except the names.

In the attached sheet, Col A to C is the data provided to me. Col D(New Coverage Start Date) and Col E(New Coverage End Date) is what I update. The logic remains the same. when there is continuation(gap of 1 day) between end date of first row and start date of second row it means the policy is continued without break. If gap is more than 1 day it means policy is not continued hence we show the dates as they are.

I have highlighted unique cases in different color. The special case is the last person "Megan" the dates are not in continuation. so the dates of "Megan" are as shown there. The macro which I wrote fails when I encounter cases like "Megan".


Let me know if the sample file is ok.

[kevvukeka]

Hi All,

Can someone please help with my above request?

Thank you..

[p45cal]

Tomorrow

[p45cal]

try:

Sub blah()
rw = 2  'adjust to the starting row of your data (exclude headers)
BlockStart = rw
BlockEnd = rw
Do Until Cells(rw, 1) = ""
  If Cells(rw, 1) = Cells(rw + 1, 1) And Cells(rw + 1, 2) - Cells(rw, 3) < 1 Then
    BlockEnd = rw + 1
  Else
    Range(Cells(BlockStart, 6), Cells(BlockEnd, 6)) = Application.Min(Range(Cells(BlockStart, 2), Cells(BlockEnd, 3)))
    Range(Cells(BlockStart, 7), Cells(BlockEnd, 7)) = Application.Max(Range(Cells(BlockStart, 2), Cells(BlockEnd, 3)))
    BlockStart = rw + 1
    BlockEnd = rw + 1
  End If
  rw = rw + 1
Loop
End Sub

or

Sub blah2()
rw = 2  'adjust to the starting row of your data (exclude headers)
Set ThisBlock = Cells(rw, 2).Resize(, 2)
Do Until Cells(rw, 1) = ""
  If Cells(rw, 1) = Cells(rw + 1, 1) And Cells(rw + 1, 2) - Cells(rw, 3) < 1 Then
    Set ThisBlock = ThisBlock.Resize(ThisBlock.Rows.Count + 1)
  Else
    ThisBlock.Offset(, 4) = Array(Application.Min(ThisBlock), Application.Max(ThisBlock))
    Set ThisBlock = Cells(rw + 1, 2).Resize(, 2)
  End If
  rw = rw + 1
Loop
End Sub

In the second one, adjust the 4 in this line to alter which columns the results are written to. At the moment they're written to columns F:G, to have them written to D:E change that 4 to a 2:
ThisBlock.Offset(, 4) = Array(Application.Min(ThisBlock), Application.Max(ThisBlock))

You can tweak the first one similarly, in which case you adjust the numbers in red below:
Range(Cells(BlockStart, 6), Cells(BlockEnd, 6)) = Application.Min(Range(Cells(BlockStart, 2), Cells(BlockEnd, 3)))
Range(Cells(BlockStart, 7), Cells(BlockEnd, 7)) = Application.Max(Range(Cells(BlockStart, 2), Cells(BlockEnd, 3)))

[kevvukeka]

Hi p45cal,



I don't have words to express how much thankful I am . I wrote a code which was beyond 90 lines but the way you handled it speaks a lot about your skill.

Simply amazing. Thanks a ton for your help. Your code works like a charm. Thanks again.