# MacLochlainns Weblog

Michael McLaughlin's Technical Blog

## Excel EOMONTH Function

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:

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)`

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