EOMONTH Problem And Alternative

I've recently had problems with files using the EOMONTH function in 2007. Files with the function in sometimes come up with the following warning message:



Once the file opens the cell with the EOMONTH function in just displays =#N/A
As a result I've created the following function to replace the EOMONTH function. The function uses the same criteria as EOMONTH but uses the word Lastday. The function is used like =Lastday(start_date,months)

Public Function Lastday(datex As Date, mthpls As Integer)

mthpls = mthpls +1

If Month(datex) + mthpls > 12 Then
mth = Month(datex) + mthpls - 12
yr = Year(datex) + 1
Else
mth = Month(datex) + mthpls
yr = Year(datex)
End If
dy = 1
Dim dayx As String
Lastday = Format(DateSerial(yr, mth, dy) - 1, "Short Date")


End Function

It's not fully tested so if you decide to use it and find a problem let me know.

Spreadsheet Help & Answers To Your Inbox

If you've ever found yourself pulling your hair out trying to get a formula to work or been trying to get conditional formatting to work with no success then my new Spreadsheet Solutions Service will be just for you. Send in your queries and for a cost of £1.50 (approx $3, €1.90) the answer will be sent directly to your inbox! Check out the Spreadsheet Solutions Service page on my site for more details.