I need to find gaps between the end date and start of the data.

Unfortunately my dates are in different columns and are grouped.

Example:

I need to check the gaps for the dates that go with each address number.
compare Effective Thru with the next rows Effective From. But do not
compare it to the next row if the address number is differerent.


Address Number Effective From Effective Thru
16436503 06/29/2007 10/31/2007
16436503 11/01/2007 12/31/2007
16436503 01/01/2008 07/21/2008
16436503 07/22/2008 07/31/2009
16452378 09/03/2007 12/31/2040
16459465 07/05/2007 10/31/2007
16459465 11/01/2007 12/31/2007
16459465 01/01/2008 12/31/2040

Try this in D2:

=IF(A2<A3,"end",B3-C2-1)

It will give you the length of the gap, or the word "end" for the
final address number in a sequence. You might like to change this to
"" in the formula.

Hope this helps.

Pete

On Aug 19, 4:33*pm, gecko123
wrote:
I need to find gaps between the end date and start of the data.

Unfortunately my dates are in different columns and are grouped.

Example:

I need to check the gaps for the dates that go with each address number.
compare Effective Thru with the next rows Effective From. *But do not
compare it to the next row if the address number is differerent.

Address Number *Effective From *Effective Thru
16436503 * * * *06/29/2007 * * *10/31/2007
16436503 * * * *11/01/2007 * * *12/31/2007
16436503 * * * *01/01/2008 * * *07/21/2008
16436503 * * * *07/22/2008 * * *07/31/2009
16452378 * * * *09/03/2007 * * *12/31/2040
16459465 * * * *07/05/2007 * * *10/31/2007
16459465 * * * *11/01/2007 * * *12/31/2007
16459465 * * * *01/01/2008 * * *12/31/2040

Perfect!

Thank you

"Pete_UK" wrote:

Try this in D2:

=IF(A2<A3,"end",B3-C2-1)

It will give you the length of the gap, or the word "end" for the
final address number in a sequence. You might like to change this to
"" in the formula.

Hope this helps.

Pete

On Aug 19, 4:33 pm, gecko123
wrote:
I need to find gaps between the end date and start of the data.

Unfortunately my dates are in different columns and are grouped.

Example:

I need to check the gaps for the dates that go with each address number.
compare Effective Thru with the next rows Effective From. But do not
compare it to the next row if the address number is differerent.

Address Number Effective From Effective Thru
16436503 06/29/2007 10/31/2007
16436503 11/01/2007 12/31/2007
16436503 01/01/2008 07/21/2008
16436503 07/22/2008 07/31/2009
16452378 09/03/2007 12/31/2040
16459465 07/05/2007 10/31/2007
16459465 11/01/2007 12/31/2007
16459465 01/01/2008 12/31/2040

You're welcome - thanks for feeding back.

Pete

On Aug 19, 6:14*pm, gecko123
wrote:
Perfect!

Thank you