I'm Having Problems with EOMONTH

[Ronni T. Vasquez M.]

Hi Everyone,

I'm creating for a company, a daily report using excel. This report is going
to be use by clients with differents language version of Excel (Spanish,
English) or differents language version of Windows.

Functions like: TODAY or NOW, works perfectly no matter what language
version of excel i'm working with. It's seems that Excel recognize the
function in the sheet and automatically convert that function to the local
language version of excel (or Windows).

However, EOMONTH (another functions i'm dealing with) doesn't works when i
try to open the report in a spanish version of Excel.

If I change the function to FIN.MES (spanish version of EOMONTH) to solve
the problem, it works perfectly, but when I try to open the report in an
english version of excel again, the function doesn't works.

It seems that excel doesn't recognize the language of this functions.

Can anyone of you help me with this?

In Both versions, I have checked the Analisys ToolPak Excel Add-in Option.

Regards,

 

[JE McGimpsey]

See one answer in your "5/22/05" post.

 

[arno]

Hi Ronni,

However, EOMONTH (another functions i'm dealing with) doesn't works
when i try to open the report in a spanish version of Excel.

sorry, I cannot solve your problem. However, as a workaround you can
use a formula to replace eomonth like

=date(year, month+1, 0)

this will give you day zero of the next month which is one day before
the first day of the next month which is the last day of the current
month. Eg. if you want to know the last day of october (the second next
end of month from today, uaaaahhh..... bad english) you would use
instead of

=eomonth(today(),2)

this formula:
=date(year(today()),month(today())+3,0)

confusing enough?

arno

 

[Blue Hornet]

Ronni,

I had a similar problem with EOMONTH not being able to compute in the
SAME spreadsheet on the SAME computer with the SAME version of Excel.
In other words, from time to time it would just fail to compute,
leaving me with a NAME? error--and I always have the Analysis TookPak
add-in active. I didn't get it, and it was driving me crazy.

So I wrote my own function and it seems to work okay. I haven't had
occasion to check its performance on other language versions, though.
Of course, it means the sheet has to be opened with "Macros Enabled",
but all of mine require that.

*************************************

Function myEOM(Target As Date, AddMonths As Integer) As Date
' Because the EOMONTH() function in the Analysis Toolpack is causing
grief
myYear = Year(Target)
myMonth = Month(Target)
myTotalMonths = ((myYear - 1900) * 12) + myMonth + AddMonths + 1

myYear = Int(myTotalMonths / 12) + 1900
If myTotalMonths Mod 12 <> 0 Then
myMonth = myTotalMonths Mod 12
Else
myMonth = 12 'Months evenly divisible by 12 means = December
myYear = Year(Target)
End If

myEOM = DateValue(myMonth & "/1/" & myYear) - 1
End Function

 

[JE McGimpsey]

While you can use worksheet functions (see my other reply), which in
general are more efficient, you could make your UDF a bit more efficient
using the DateSerial method.

This routine also corrects the result if the 1904 date system is being
used:

Public Function myEOM(BaseDate As Date, AddMonths As Long) As Date
Dim dDateCorrection As Double
With Application
If TypeOf .Caller Is Range Then _
dDateCorrection = 1462 * .Caller.Parent.Parent.Date1904
End With
myEOM = DateSerial(Year(BaseDate), Month(BaseDate) + _
AddMonths + 1, 0) + dDateCorrection
End Function

 

[Ronni T. Vasquez M.]

Hi,

Thanks for your help, JE McGimpsey and you have given me the same solution,
it works and I'm going to use it as you both show me.

However I'll keep reading about these subject to learn more.

If I found something else, i'll put it in this forum.

Regards

 

[Ronni T. Vasquez M.]

Hi Blue,

Thank for your help,

I'm going to use your function in a spanish version and i'll let you know
what happen with it...

After reading it, i think it should work...

Regards,

 

[Ronni T. Vasquez M.]

Hi JE McGimpsey,

Thanks for your comments, this solutions is a little more difficult to
understand so i have nothing to say about the solution itself until I read a
little bit more about excel programing object. I enjoy learning these kind
of things, so thanks for helping us with this...

I'll read carefully this solutions,

Regards

 

[Blue Hornet]

Actually ... now that I understand JEM's worksheet function (I haven't
worked with the "day 0" concept before), I'd be inclined to try his
method.