MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘Excel Development’ tag

Excel EOMONTH Function

without comments

I fielded a question from blog reader on a three year old post about the EOMONTH (End Of Month) function in Excel. The reader want to write a mega-function (a term for putting two or more functions together to solve a problem, according to Mr. Excel). The function would return the first day of any month for the first day of the month and the first day of the next month for any day after the first day of the last month.

I wrote a quick little solution for the user, as shown below:

=IF(A1=EOMONTH(A1,-1)+1,EOMONTH(A1,-1)+1,EOMONTH(A1,0)+1)

The results would look like:

KevinExcelForumula

The user ran into a problem with the example because the date was being calculated and not an integer value. The formula returned the first day of the next month when it should have returned the first day of the current month. That meant the source value in the cell wasn’t an integer. It was a real number. Integer values in Excel are numeric values for 12:00:00 A.M. of any day, and real numbers can be any time of the day.

Unfortunately, the EOMONTH function is a pseudo-overloaded function, and the EOMONTH function takes an int or double (integer or real number) as it’s parameter. That’s because Excel only supports a NUMBER data type, which can be an integer or real number.

The EOMONTH function always returns an integer. The IF function I provided was designed to compare an integer-based date cell value against the result of the EOMONTH function. It wasn’t robust enough to support a comparison of 11/1/13 at 12:01 A.M. against the result of the EOMONTH function (11/1/13 at 12:00 A.M.). That’s why it returned the first of the next month instead of the first of the current month.

The more complete solution requires using the TRUNC function around the source date (B4 or C4 in the following example) to ensure the IF statement compares dates not time-stamps.

=IF(TRUNC(B4)=EOMONTH(B4,-1)+1,EOMONTH(B4,-1)+1,EOMONTH(B4,0)+1)

KevinExcelFormula2

You should note that the TRUNC function effectively rounds down to the integer and removes the fractional portion of the real number before making a comparison. It’s an explicit casting process when we take proactive measures to ensure the value before the IF function compares it.

Written by maclochlainn

July 26th, 2014 at 11:20 pm

Excel 1st Day of Next Month

with 50 comments

I had an interesting reaction when I told my students they had to create a dynamic Excel model that required them to enter the months of the year without AutoFill feature. They were stumped. They couldn’t find a function to perform it for them.

It’s really quite easy, the EDATE function lets you find it directly. Enter any day in the current month as the first parameter and one as the number of months to add as the second parameter to the function. VoilĂ , you have a function to add a month to any date. The only time it returns the first of the next month is when the source date was the first day of the current month.

Here’s the solution when cell B1 contains January 14, 2011 and you want cell C1 calculated as the fourteenth day of February in the same year or February 14, 2011:

=EDATE(B1,1)

Here’s the solution when cell B1 contains January 14, 2011 and you want cell C1 calculated as the fourteenth day of December in the prior year or December 14, 2010:

=EDATE(B1,-1)

You can also use the EOMONTH function to find the first or last day of the month. It lets you find it the first day of the next month regardless of the start date. All you need is a simple trick.

Enter any day in the current month as the first parameter and zero as the number of months to add as the second parameter to the function, and then add one.

Here’s the solution when cell B1 contains any day in January and you want cell C1 calculated as the last day of January in the same year (rounding up).

=EOMONTH(B1,0)

Here’s the solution when cell B1 contains any day in January and you want cell C1 calculated as the first day of January in the same year (rounding down).

=EOMONTH(B1,-1)+1

A subsequent question asked how to calculate the 15th day of the current month, and here’s the formula:

=EOMONTH("10-Jul-2012",-1)+15

Hope this helps them and others looking for the trick.

Written by maclochlainn

February 1st, 2011 at 7:54 pm

LIKE operator in Excel

with 2 comments

While discussing the limitations of wild card comparisons in the Microsoft Excel’s IF function, I resolved to share my ISLIKE function. It acts like the LIKE operator in SQL. You can’t use the word like as the function name because it’s a reserved word in Visual Basic for Applications (VBA).

The function lets you compare any string to see if it is contained in another string. The default mode is case-insensitive, and the override mode case-sensitive. The optional third element lets you override the default comparison method.

You should remember or note that a number 0 is always evaluated as false, and any other number (negative or positive) is always true. You can put the ISLIKE function in a macro sheet or an Add-in library. I recently posted instructions on how to create your own Add-in library.

Here is the VBA code for the user defined ISLIKE function:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
Function ISLIKE( inValue As Variant _
               , hasValue As Variant _
               , Optional caseSensitive As Integer = 0)
 
  ' Define a logical return variable and assign a default value.
  Dim logicReturn As Boolean
  logicReturn = False
 
  If caseSensitive Then
 
    ' Finds a case-insensitive value as a component in a larger value.
    If inValue Like "*" + hasValue + "*" Then
      logicReturn = True
    End If
 
  Else
 
    ' Finds a case-sensitive value as a component in a larger value.
    If UCase(inValue) Like "*" + UCase(hasValue) + "*" Then
      logicReturn = True
    End If
 
  End If
  ' Return the logical value.
  ISLIKE = logicReturn
 
End Function

Line numbers 12 and 19 use the "*", which is equivalent to the regular expression of "\.*". I’ve no idea why they made this choice, and it’s not well explained on Microsoft’s web site covering SmartTags.

You can shorten the program and make it slightly less efficient by performing the case sensitive match twice. The following doesn’t rely on nested block IF statements. You also must change the third parameter to a Boolean or the Not operator won’t work correctly.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Function ISLIKE( lookupValue As Variant _
               , containsValue As Variant _
               , Optional caseSensitive As Boolean = 0)
 
  ' Define a logical return variable and assign a default value.
  Dim logicReturn As Boolean
  logicReturn = False
 
  ' Finds a case-insensitive value as a component in a larger value.
  If caseSensitive And _
     lookupValue Like "*" + containsValue + "*" Then
      logicReturn = True
 
  ' Finds a case-sensitive value as a component in a larger value.
  ElseIf Not caseSensitive And _
    UCase(lookupValue) Like "*" + UCase(containsValue) + "*" Then
      logicReturn = True
 
  End If
 
  ' Return the logical value.
  ISLIKE = logicReturn
 
End Function

Somebody asked me, “How this can be useful?” That meant from their perspective, how is it useful beyond a comparison of cell values. A quick example of that additional utility can be demonstrated from an early post. In that post on replacing a VLOOKUP function with a UDF, there’s compound logic used to find the occurrence of a substring in a string. The ISLIKE function can simplify that logic.

Here’s that snippet of the code from that referenced post. The snippet evaluates whether the base string is greater than one character and checks the second character of the string to see if it is a - (minus sign).

58
ElseIf Len(LetterGrades(i)) > 1 And Mid(LetterGrades(i), 2, 1) = "-" Then

You can replace the compound logic with a call to the LIKE function. It would replace line 58 as follows:

58
ElseIf ISLIKE(LetterGrades(i),"-") Then

The logic of the function examines to see if the first parameter contains the second parameter. This is what is meant by like. In the following illustration, you examine if a string contains a substring (or is like a substring). The cell A2 is also assigned a Range name. Range names can increase the readability of your models and functions. All but the last one perform case insensitive comparisons that find whether the substring in cell A2 is found with cell range of the B5:B10. The B10 formula performs a case sensitive match.

Another logic comparison uses grades to see if a minus sign is found in them. The example compares the cell values in A1 and A2 against the B1 and B2 values. Returning true for one and false for the other cases in column C of the respective rows.

Here are the calls to the ISLIKE user defined function. The first one is case-insensitive and the second case-sensitive, but there’s no case sensitivity with a minus sign, -.

Spreadsheet Functions
Cell Formula
C1 =ISLIKE(A1,B1)
C2 =ISLIKE(A2,B2,TRUE)

As always, I hope this helps. If you’ve comments or alternative approaches, please let me know.

Written by maclochlainn

June 4th, 2010 at 11:28 am

Excel string parsing

with 14 comments

Parsing strings isn’t the easiest thing to do in Excel 2007 or Excel 2008 but it is an important thing to know how to do. You’ll learn how to parse a set names into first, middle and last names. You can find the data set for these examples at the bottom of the blog post.

Written by maclochlainn

February 27th, 2009 at 10:15 pm