Hi,
I am checking monthly claims report to ensure that the payment of monthly
benefit is not overlapping. Using the table below, could someone give me a
formula to check that the payment period from and payment period to is in the
right sequence.

Monthly Benefit Period from Period to Amount Paid
840.00 26/12/2007 23/09/2008 7,173.33
840.00 24/09/2008 16/12/2008 767.92
840.00 17/12/2008 10/02/2009 503.90

Thanks

Brenda

Assuming you have dates in ColB and ColC with the headers in Row 1; try the
below formula in ColE or any available column.

In E1
=IF(B2C2,"Date not in sequence","")
(copy down as required...)

If this post helps click Yes
---------------
Jacob Skaria


"brenda" wrote:

Hi,
I am checking monthly claims report to ensure that the payment of monthly
benefit is not overlapping. Using the table below, could someone give me a
formula to check that the payment period from and payment period to is in the
right sequence.

Monthly Benefit Period from Period to Amount Paid
840.00 26/12/2007 23/09/2008 7,173.33
840.00 24/09/2008 16/12/2008 767.92
840.00 17/12/2008 10/02/2009 503.90

Thanks

Brenda

With your dates in columns B and C and your data starting on row 2...

Enter this formula in E3 and copy down as needed:

=IF(B3=C2+1,"Ok","Not In Sequence")

No error checking for empty cells.

--
Biff
Microsoft Excel MVP


"brenda" wrote in message
...
Hi,
I am checking monthly claims report to ensure that the payment of monthly
benefit is not overlapping. Using the table below, could someone give me
a
formula to check that the payment period from and payment period to is in
the
right sequence.

Monthly Benefit Period from Period to Amount Paid
840.00 26/12/2007 23/09/2008 7,173.33
840.00 24/09/2008 16/12/2008 767.92
840.00 17/12/2008 10/02/2009 503.90

Thanks

Brenda

It works. Thank you so much. This formula will save a lot of my time.
However, in my first example, the payments are for 1 claim only. What if in
a spreadsheet, there are different claim numbers. How do I modify the
formula as in my 2nd example below.

Claim No Sum Insured Period From Period To Amount Payable
24895 $854.49 16/02/2009 15/03/2009 824.00
24895 $854.49 16/03/2009 15/04/2009 854.49
24895 $854.49 16/04/2009 15/05/2009 854.49
24895 $854.49 16/05/2009 15/06/2009 854.49
26243 $836.00 10/03/2009 9/04/2009 836.00
26243 $836.00 10/04/2009 9/05/2009 836.00
26243 $836.00 10/05/2009 9/06/2009 836.00




"T. Valko" wrote:

With your dates in columns B and C and your data starting on row 2...

Enter this formula in E3 and copy down as needed:

=IF(B3=C2+1,"Ok","Not In Sequence")

No error checking for empty cells.

--
Biff
Microsoft Excel MVP


"brenda" wrote in message
...
Hi,
I am checking monthly claims report to ensure that the payment of monthly
benefit is not overlapping. Using the table below, could someone give me
a
formula to check that the payment period from and payment period to is in
the
right sequence.

Monthly Benefit Period from Period to Amount Paid
840.00 26/12/2007 23/09/2008 7,173.33
840.00 24/09/2008 16/12/2008 767.92
840.00 17/12/2008 10/02/2009 503.90

Thanks

Brenda

Try this:

=IF(A3=A2,IF(C3=D2+1,"Ok","Not In Sequence"),"")

--
Biff
Microsoft Excel MVP


"brenda" wrote in message
...
It works. Thank you so much. This formula will save a lot of my time.
However, in my first example, the payments are for 1 claim only. What if
in
a spreadsheet, there are different claim numbers. How do I modify the
formula as in my 2nd example below.

Claim No Sum Insured Period From Period To Amount Payable
24895 $854.49 16/02/2009 15/03/2009 824.00
24895 $854.49 16/03/2009 15/04/2009 854.49
24895 $854.49 16/04/2009 15/05/2009 854.49
24895 $854.49 16/05/2009 15/06/2009 854.49
26243 $836.00 10/03/2009 9/04/2009 836.00
26243 $836.00 10/04/2009 9/05/2009 836.00
26243 $836.00 10/05/2009 9/06/2009 836.00




"T. Valko" wrote:

With your dates in columns B and C and your data starting on row 2...

Enter this formula in E3 and copy down as needed:

=IF(B3=C2+1,"Ok","Not In Sequence")

No error checking for empty cells.

--
Biff
Microsoft Excel MVP


"brenda" wrote in message
...
Hi,
I am checking monthly claims report to ensure that the payment of
monthly
benefit is not overlapping. Using the table below, could someone give
me
a
formula to check that the payment period from and payment period to is
in
the
right sequence.

Monthly Benefit Period from Period to Amount Paid
840.00 26/12/2007 23/09/2008 7,173.33
840.00 24/09/2008 16/12/2008 767.92
840.00 17/12/2008 10/02/2009 503.90

Thanks

Brenda