Thursday, 5 November 2009

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
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.

Add to Technorati Favorites

No comments: