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

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","")

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 szept. 19, 16:42, "Rick Rothstein"
wrote:
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)

These Excel sheets are exported from SAS dataset. The dates are like
'10Aug2011'.

These Excel sheets are exported from SAS dataset.
The dates are like '10Aug2011'.

Does that mean there are no slashes in the dates like you showed us in your
original message???

Also, are those apostrophes around the values in the cell as you are now
showing us?

If you want to get an answer to your question, you must show us **EXACTLY**
what your data looks like (nearly what it looks like is not good enough).

Rick Rothstein (MVP - Excel)

On szept. 19, 20:13, "Rick Rothstein"
wrote:
These Excel sheets are exported from SAS dataset.
The dates are like '10Aug2011'.

Does that mean there are no slashes in the dates like you showed us in your
original message???

Yes.

Also, are those apostrophes around the values in the cell as you are now
showing us?

No.

If you want to get an answer to your question, you must show us **EXACTLY**
what your data looks like (nearly what it looks like is not good enough).

Rick Rothstein (MVP - Excel)

Sorry for the inconviniences. I recognized the differences on 19 Sep.

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.

These Excel sheets are exported from SAS dataset. The dates are
like '10Aug2011'.

Does that mean there are no slashes in the dates like you showed us in
your
original message???

Yes.

Can you verify for us again that the cells containing the dates like
10Aug2011 are really formatted as "General" and not "Text". I ask because
when I enter 10Aug2011 into my copy of XL2003, it converts that entry to a
date format immediately (actually, a Custom Format of d-mmm-yy, but that is
still a date, not "General").

Rick Rothstein (MVP - Excel)

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.

These Excel sheets are exported from SAS dataset. The dates are
like '10Aug2011'.

Does that mean there are no slashes in the dates like you showed us in
your
original message???

Yes.

Can you verify for us again that the cells containing the dates like
10Aug2011 are really formatted as "General" and not "Text". I ask because
when I enter 10Aug2011 into my copy of XL2003, it converts that entry to a
date format immediately (actually, a Custom Format of d-mmm-yy, but that is
still a date, not "General").

Rick Rothstein (MVP - Excel)

On szept. 20, 17:22, "Rick Rothstein"
wrote:
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.

These Excel sheets are exported from SAS dataset. The dates are
like '10Aug2011'.

Does that mean there are no slashes in the dates like you showed us in
your
original message???

Yes.

Can you verify for us again that the cells containing the dates like
10Aug2011 are really formatted as "General" and not "Text". I ask because
when I enter 10Aug2011 into my copy of XL2003, it converts that entry to a
date format immediately (actually, a Custom Format of d-mmm-yy, but that is
still a date, not "General").

Rick Rothstein (MVP - Excel)

Yes, I confirm that the dates are like 10Aug2011 formatted as General,
not as Text. "My" Excel doesn't manage or transmit it as Date. Even if
I format these cells into date, they don't change their format.

Yes, I confirm that the dates are like 10Aug2011
formatted as General, not as Text.

I'm beginning to think you have more in that cell than the date you are
showing us. I believe you have one or more trailing non-breaking spaces
(ASCII 160) in the cell. Try this and see if it straightens things out.
Select your cell or cells with those "dates", then press CTRL+H to bring up
the Replace dialog box. Put the cursor in the "Find what" field (make sure
the field is empty) and then hold down the ALT key while pressing the
numbers 160 on the NUMBER PAD, not on the main keyboard. Make sure the
"Replace with" field is empty. Next, click the "Options" button to display
all the options for this dialog box (assuming they are not already
displayed) and make sure the "Match entire cell contents" check box is NOT
checked, then click OK. If I am correct in my assumption, then your dates
should have been converted into real dates.

Rick Rothstein (MVP - Excel)