Missing a month or more multiple columns

Jer-XL-emy

Board Regular
Joined
Aug 27, 2007
Messages
60
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
 

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
Joined
Jul 15, 2008
Messages
1,348
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
Joined
Aug 27, 2007
Messages
60
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
Joined
Jul 15, 2008
Messages
1,348
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
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top