Missing a month or more multiple columns

[Jer-XL-emy]

Hi excel pros,

I have an excel crossword puzzle for you.

I have four columns each containing a date (formated - YYYY-MM-DD)

I am looking to see if (it is at all possible) a formula can tell me when there is more then a difference of a month or greater than a month missing with in a range of months.

For example;

If the four columns looked like this

2008-06-05 | 2008-08-05 | 2008-09-05 | 2008-07-05

There is clearly a month difference between 2008-06-05 and 2008-09-05, but that is not what I am after as there are months inbetween.

What I am looking for is if there is a month missing in the range of the four months -

I hope I explained this well enough

Thank you in advance

 

[PCL]

Without macro a possibility could be:
Assuming dates in A1:D1
In K1 =MIN(MONTH(A1:D1)) confirm with Control + Shift + Enter
In L1 =K1+1, M1 =L1+1, N1 =M1+1
In O1=SUM((ISERROR(MATCH(MONTH(A1:D1),$K1:$N1,0)))*1) confirm with Control + Shift + Enter

 

[Jer-XL-emy]

Thanx PCL,

I see your logic, while the formula (arrays) work I get mostly zeros so not sure how the zeros help of what they mean
My fault for not explaining clearly enough

 

[PCL]

Assuming:
A1:D1 = <TABLE style="WIDTH: 248pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=328 border=0 x:str><COLGROUP><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" span=4 width=82><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 62pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right width=82 height=17 x:num="39604">5-Jun-08</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 62pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right width=82 x:num="39665">5-Aug-08</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 62pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right width=82 x:num="39696">5-Sep-08</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 62pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right width=82 x:num="39634">5-Jul-08</TD></TR></TBODY></TABLE>
K1: N1 =
6 7 8 9
Next formula
=SUM((ISERROR(MATCH(MONTH(A1:D1),$K1:$N1,0)))*1)
gives 0

A2:D2=
<TABLE style="WIDTH: 248pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=328 border=0 x:str><COLGROUP><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" span=4 width=82><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 62pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right width=82 height=17 x:num="39604">5-Jun-08</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 62pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right width=82 x:num="39543">5-Apr-08</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 62pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right width=82 x:num="39696">5-Sep-08</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 62pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right width=82 x:num="39634">5-Jul-08</TD></TR></TBODY></TABLE>
K2: N2 =
6 7 8 9
Next formula
=SUM((ISERROR(MATCH(MONTH(A1:D1),$K1:$N1,0)))*1)
gives 1
If you want to use an UDF:

Option Explicit
Function Month_Check(MyRg As Range)
Dim C As Object
Dim Month_Tot(4) As Variant
Dim Month_Min As Variant
Dim A As Integer
Dim B As Integer
Dim I As Integer
Dim J As Integer
    Month_Min = Month(MyRg(1))
    Month_Check = 0
    A = 1
    For Each C In MyRg
        If (Month(C.Value) < Month_Min) Then Month_Min = Month(C.Value)
        A = A * Month(C.Value)
    Next C
    B = Month_Min * (Month_Min + 1) * (Month_Min + 2) * (Month_Min + 3)
    If (A <> B) Then Month_Check = 1
End Function