CODEWITHSUNDEEP
vbaexcelexcel-2010
apdm
apdm

Reputation: 1330

What's the best logic to return TRUE in VBA if month NAMES are 1 apart?

I want to return True for the following examples:

Example 1 

Date 1: 7/31/14   
Date 2: 8/1/14

Example 2 

Date 1: 12/31/07  
Date 2: 1/1/16      (notice one year is 2007)

and False for the following example:

Example 3 

Date 1: 7/31/14
Date 2: 9/1/14

What is the smartest way to go about this? I know I can use the Month() function and test whether the months are either different by 1 or 11, but that seams like a bad solution

Upvotes: 0

Views: 73

Answers (3)

Bond
Bond

Reputation: 16311

I think the easiest way would be to combine Abs() with Mod() in the following formula:

Abs(Month(Date1) - Month(Date2)) Mod 10 = 1

For example:

Dim Date1 As Date, Date2 As Date

Date1 = DateSerial(2014, 7, 31)
Date2 = DateSerial(2014, 8, 1)
Debug.Print Abs(Month(Date1) - Month(Date2)) Mod 10 = 1

Date1 = DateSerial(2007, 12, 31)
Date2 = DateSerial(2016, 1, 1)
Debug.Print Abs(Month(Date1) - Month(Date2)) Mod 10 = 1

Date1 = DateSerial(2014, 7, 31)
Date2 = DateSerial(2014, 9, 1)
Debug.Print Abs(Month(Date1) - Month(Date2)) Mod 10 = 1

Output:

True
True
False

Upvotes: 0

LimaNightHawk
LimaNightHawk

Reputation: 7093

If you don't care about the year, then it seem's perfectly legit (and even best) to use the build in Month function, like this:

Public Function AreMonthsOneApart(date1 As Date, date2 As Date) As Boolean

    Dim lMonthsApart As Long
    lMonthsApart = Abs(Month(date1) - Month(date2))
    AreMonthsOneApart = (lMonthsApart = 1 Or lMonthsApart = 11)

End Function

In fact, using anything besides the Month function is probably going to be more complex.

(Even @Jeeped's solution uses Month in his calculations. It's going to be very hard to get around--Use Month!)

Upvotes: 1

user4039065
user4039065

Reputation:

Simple boolean logic and maths should suffice.

Sub dts()
    Dim Date1 As Date, Date2 As Date

    Date1 = DateSerial(2014, 7, 31)
    Date2 = DateSerial(2014, 8, 1)
    Debug.Print CBool(Abs((Month(Date1) - (Month(Date1) = 1) * 12) - (Month(Date2) - (Month(Date2) = 1) * 12)) = 1)

    Date1 = DateSerial(2007, 12, 31)
    Date2 = DateSerial(2016, 1, 1)
    Debug.Print CBool(Abs((Month(Date1) - (Month(Date1) = 1) * 12) - (Month(Date2) - (Month(Date2) = 1) * 12)) = 1)

    Date1 = DateSerial(2014, 7, 31)
    Date2 = DateSerial(2014, 9, 1)
    Debug.Print CBool(Abs((Month(Date1) - (Month(Date1) = 1) * 12) - (Month(Date2) - (Month(Date2) = 1) * 12)) = 1)

End Sub

Results:

dts
True
True
False

You added both and to your question. If used as a worksheet formula, remember that TRUE is 1, not -1 as in a VBA True.

Upvotes: 3

Related Questions