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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,322
Members
448,564
Latest member
ED38

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
Back
Top