Help on EOMONTH error (have Analysis Pak installed)

[SteveS]

I am importing date into excel via an external data query with Access. In
excel I have the following formula:

=IF(ISERROR((EOMONTH(B2,-1))+1),0,(EOMONTH(B2,-1)+1))
where B2 contains a date imported from Access (in date format, not text).

When I first set up the formula, it worked fine and I got the desired
result. However, when I refresh my data the result changes to 1/0/1900. In
other words, it's giving me "0" because it's saying it's an ISERROR. When I
change the formula to "=EOMONTH(B2,-1)+1" I get a #NAME error message.

I find this extremely odd because:
1) I have the Analysis Tookpack Installed (I'm running Excel 2003)
2) The formula worked fine before.
3) When I undo my data refresh, the formula still does not work

HELP!!! Is there a better way to return the first day of the month without
risking this issue with EOMONTH?

Thanks!

 

[Ron Rosenfeld]

I am importing date into excel via an external data query with Access. In
excel I have the following formula:

=IF(ISERROR((EOMONTH(B2,-1))+1),0,(EOMONTH(B2,-1)+1))
where B2 contains a date imported from Access (in date format, not text).

When I first set up the formula, it worked fine and I got the desired
result. However, when I refresh my data the result changes to 1/0/1900. In
other words, it's giving me "0" because it's saying it's an ISERROR. When I
change the formula to "=EOMONTH(B2,-1)+1" I get a #NAME error message.

I find this extremely odd because:
1) I have the Analysis Tookpack Installed (I'm running Excel 2003)
2) The formula worked fine before.
3) When I undo my data refresh, the formula still does not work

HELP!!! Is there a better way to return the first day of the month without
risking this issue with EOMONTH?

Thanks!

The first day of a month, with any date of that month in A1, can be given by
the non-ATP formula:

=A1-DAY(A1)+1



--ron

 

[JMB]

Out of curiosity, is the ATP still enabled? Check Tools/Add-Ins -Analysis
Toolpak is checked?

 

[SteveS]

Yes, it is. One minute it worked, and the next it wouldn't, and I can't
imagine the Toolpak would disable itself.

I ended up putting a formula in access and importing the start date of the
month as part of the data query.

This has also happened on one other spreadsheet of mine (wasn't working with
Access data on that one. It was getting the start date a cell on another
sheet, and working fine. However, if I went into the cell with the EOMONTH
forumla and then exited (either hitting the check mark or pressing Enter) it
would do the same thing. I have not re-visited that spreadsheet to see if
it's still doing that.