Dear All,
I am sorry that I could not find any better subject for this post. I
need a solution for the following counting. I have (identification)
numbers (each number can occur frequently) in column B and dates for
the ID numbers in column G. I would indicate in column L if months in
the dates are continously exist for the numbers. If there is a gap
between 2 month in the column (like G5=11/Jun/2011 and G6=05/Aug/2011)
I need an indication for example "There is a GAP".

Well I am not sure if I was clear, here is a part of my table.

B5=460050001 G5=11/Jun/2011
B6=460050001 G6=12/Jul/2011
B7=460050001 G7=10/Aug/2011
B8=460010003 G8=05/Dec/2010
B9=460010003 G9=01/Jan/2011
B10=460010003 G10=05/Mar/2011
B11=460010003 G11=06/May/2011
etc.

In this example there is a gap for ID 460010003 at row #10, so L10
should indicate "GAP". My problem is how to automate this if I do not
know which is the last row. The 1st row is #5. There is no any blank
cells from row #5.

Thank you for anyone's help in advance.
Tamas

On 17 sep, 14:43, Konczér, Tamás wrote:
Dear All,
I am sorry that I could not find any better subject for this post. I
need a solution for the following counting. I have (identification)
numbers (each number can occur frequently) in column B and dates for
the ID numbers in column G. I would indicate in column L if months in
the dates are continously exist for the numbers. If there is a gap
between 2 month in the column (like G5=11/Jun/2011 and G6=05/Aug/2011)
I need an indication for example "There is a GAP".

Well I am not sure if I was clear, here is a part of my table.

B5=460050001 * * * * *G5=11/Jun/2011
B6=460050001 * * * * *G6=12/Jul/2011
B7=460050001 * * * * *G7=10/Aug/2011
B8=460010003 * * * * *G8=05/Dec/2010
B9=460010003 * * * * *G9=01/Jan/2011
B10=460010003 * * * *G10=05/Mar/2011
B11=460010003 * * * *G11=06/May/2011
etc.

In this example there is a gap for ID 460010003 at row #10, so L10
should indicate "GAP". My problem is how to automate this if I do not
know which is the last row. The 1st row is #5. There is no any blank
cells from row #5.

Thank you for anyone's help in advance.
Tamas

Hi
Why don't use Excel functions IF & DATEDIF to solve your problem ?
=IF(DATEDIF(G5,G6,"m")=1,"GAP","")

http://www.excel-exercice.com/en/function-if
http://www.excel-exercice.com/en/function-datedif

I have(identification) numbers (each number can occur frequently)
in column B and dates for the ID numbers in column G. I would
indicate in column L if months in the dates are continously exist
for the numbers. If there is a gap between 2 month in the column
(like G5=11/Jun/2011 and G6=05/Aug/2011) I need an indication for
example "There is a GAP".

Here is a part of my table.

B5=460050001 G5=11/Jun/2011
B6=460050001 G6=12/Jul/2011
B7=460050001 G7=10/Aug/2011
B8=460010003 G8=05/Dec/2010
B9=460010003 G9=01/Jan/2011
B10=460010003 G10=05/Mar/2011
B11=460010003 G11=06/May/2011
etc.

In this example there is a gap for ID 460010003 at row #10, so L10
should indicate "GAP". My problem is how to automate this if I do
not know which is the last row. The 1st row is #5. There is no any
blank cells from row #5.

Does this formula, placed in L5 and copied down (even past your current
data), do what you want...

=IF(OR(B6="",B5<B6),"",IF(MOD(MONTH(G6)-MONTH(G5),12)=1,"","GAP"))

Rick Rothstein (MVP - Excel)

On szept. 17, 16:43, "Rick Rothstein"
wrote:
I have(identification) numbers (each number can occur frequently)
in column B and dates for the ID numbers in column G. I would
indicate in column L if months in the dates are continously exist
for the numbers. If there is a gap between 2 month in the column
(like G5=11/Jun/2011 and G6=05/Aug/2011) I need an indication for
example "There is a GAP".

Here is a part of my table.

B5=460050001 * * * * *G5=11/Jun/2011
B6=460050001 * * * * *G6=12/Jul/2011
B7=460050001 * * * * *G7=10/Aug/2011
B8=460010003 * * * * *G8=05/Dec/2010
B9=460010003 * * * * *G9=01/Jan/2011
B10=460010003 * * * *G10=05/Mar/2011
B11=460010003 * * * *G11=06/May/2011
etc.

In this example there is a gap for ID 460010003 at row #10, so L10
should indicate "GAP". My problem is how to automate this if I do
not know which is the last row. The 1st row is #5. There is no any
blank cells from row #5.

Does this formula, placed in L5 and copied down (even past your current
data), do what you want...

=IF(OR(B6="",B5<B6),"",IF(MOD(MONTH(G6)-MONTH(G5),12)=1,"","GAP"))

Rick Rothstein (MVP - Excel)

Dear Rick and Frederic,
I did not mentioned that I use Excel2003 SP3. Unfortunately the above
mentioned formulas (both) don't work: Excel indicates a formula error
highlighting B5.
Anyway thanks for dealing with my issue.

I did not mentioned that I use Excel2003 SP3. Unfortunately
the above mentioned formulas (both) don't work: Excel
indicates a formula error highlighting B5.

I too am using XL2003 SP3 and the formula I posted works fine (no error)
here on my system. Did you copy/paste my code into your system or did you
retype it? If you copied it, you may have picked up some stray trailing junk
spaces and/or line feeds that would have to be removed; if you retyped it,
then perhaps you missed a character in the process. Again, I just tested it
to make sure... the formula works here on my system without generating any
errors.

Rick Rothstein (MVP - Excel)

On szept. 17, 20:52, "Rick Rothstein"
wrote:
I did not mentioned that I use Excel2003 SP3. Unfortunately
the above mentioned formulas (both) don't work: Excel
indicates a formula error highlighting B5.

I too am using XL2003 SP3 and the formula I posted works fine (no error)
here on my system. Did you copy/paste my code into your system or did you
retype it? If you copied it, you may have picked up some stray trailing junk
spaces and/or line feeds that would have to be removed; if you retyped it,
then perhaps you missed a character in the process. Again, I just tested it
to make sure... the formula works here on my system without generating any
errors.

Rick Rothstein (MVP - Excel)

I copy-pasted the formula and was careful to avoid pick up any spaces,
etc. However I've just recognized that in the original table coulmn B
and G are formatted as general instead of numbers and dates, maybe
this is the cause of the problem.

Thank you.
Tamas

However I've just recognized that in the original table coulmn B
and G are formatted as general instead of numbers and dates

How can Column G be formatted as General and be displaying dates like
10/Aug/2011? My version of Excel converts entries like those directly into
real dates.

Rick Rothstein (MVP - Excel)

On Sat, 17 Sep 2011 05:43:07 -0700 (PDT), Konczér, Tamás wrote:

Dear All,
I am sorry that I could not find any better subject for this post. I
need a solution for the following counting. I have (identification)
numbers (each number can occur frequently) in column B and dates for
the ID numbers in column G. I would indicate in column L if months in
the dates are continously exist for the numbers. If there is a gap
between 2 month in the column (like G5=11/Jun/2011 and G6=05/Aug/2011)
I need an indication for example "There is a GAP".

Well I am not sure if I was clear, here is a part of my table.

B5=460050001 G5=11/Jun/2011
B6=460050001 G6=12/Jul/2011
B7=460050001 G7=10/Aug/2011
B8=460010003 G8=05/Dec/2010
B9=460010003 G9=01/Jan/2011
B10=460010003 G10=05/Mar/2011
B11=460010003 G11=06/May/2011
etc.

In this example there is a gap for ID 460010003 at row #10, so L10
should indicate "GAP". My problem is how to automate this if I do not
know which is the last row. The 1st row is #5. There is no any blank
cells from row #5.

Thank you for anyone's help in advance.
Tamas

If the data in Col G is real dates, then

L6: =IF(AND((MONTH(G6)-MONTH(G5))1,B6=B5),"Gap","")

IF the data in Col G is text or real dates (or mixed), then

L6: =IF(AND((MONTH(--G6)-MONTH(--G5))1,B6=B5),"Gap","")