month gaps counting through whole column

[Konczér, Tamás]

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

 

[Frederic LE GUEN - MVP Excel]

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

 

[Rick Rothstein]

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)

 

[Rick Rothstein]

Why don't use Excel functions IF & DATEDIF to solve your problem?

I know others disagree with me on this, but I would recommend not using
DATEDIF, especially if the worksheet will be used for something important.
Here is a post I have given in the past explaining why I am making this
recommendation...

You might want to reconsider using the DATEDIF function. It is an
undocumented (and, thus, probably an unsupported) Excel function which
appears to be broken in XL2007 at Service Pack 2. Someone recently posted
this (paraphrased) message as part of a newsgroup question...

*********************************************************************
=DATEDIF(DATE(2009,6,27),DATE(2012,1,5),"md")

In XL2003, the above formula gives me the correct answer of 9. However,
in Excel 2007, it gives me 122. The 122 increases in value until it
hits 143 on 1/26/2012 and then, on 1/27/2012, the difference becomes 0.
*********************************************************************

An informal survey of fellow MVPs shows the above formula works correctly in
the initial release of XL2007 and its SP1, but does not work correctly in
SP2; hence, it appears to be broken at that level. The problem is that the
extent of the breakage is unknown (and probably indeterminable). In
addition, I would say, being an undocumented (and, thus, probably an
unsupported) function, the odds of Microsoft spending the time to search
down and fix whatever broke is slim. In addition, again because it is
probably unsupported, the extent of any future breakage in the function due
to other code change Microsoft makes elsewhere in Excel is unknowable...
something that works today may not work tomorrow and Microsoft will probably
never fix it. This would seem to mean that DATEDIF cannot be counted on to
work correctly from XL2007 SP2 onward. And even if Microsoft did fix the
problem in a subsequent Service Pack, any of your users who remained at SP2
would be subjected to incorrect result.

Rick Rothstein (MVP - Excel)

 

[Konczér, Tamás]

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.

 

[Rick Rothstein]

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)

 

[Konczér, Tamás]

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

 

[Rick Rothstein]

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)

 

[Konczér, Tamás]

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'.

 

[Rick Rothstein]

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)

 

[Konczér, Tamás]

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.

 

[Rick Rothstein]

How can Column G be formatted as General and be displaying dates

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)

 

[Rick Rothstein]

How can Column G be formatted as General and be displaying dates

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)

 

[Konczér, Tamás]

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.

 

[Rick Rothstein]

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)