month gaps counting through whole column
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)
|