Missing a month or more multiple columns

Jer-XL-emy

Board Regular
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

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

PCL

Well-known Member
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

Board Regular
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

Well-known Member
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:
Code:
``````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``````

Replies
6
Views
95
Replies
6
Views
93
Replies
1
Views
91
Replies
17
Views
788
Replies
8
Views
261

1,132,668
Messages
5,654,638
Members
418,147
Latest member
Shnn028

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back