EDATE Function disappears intermittently

[Wendy]

Hi all,

Hope somebody knows the answer to this one.

I have a new computer set up, which has Excel 2003 installed (11.8237.8221)
SP3.
The Analysis Tool Pack has been added into the installation.

The entire office uses 2003, there are no 2007 implementations to confuse
things, although the compatability packs have been installed on individual
computers.

I have a user that accesses a 2003 spreadsheet, which uses the EDATE format.

Intermittently (eg once every few days/once a week), the user notes that
instead of the EDATE function working correctly, the #NAME thing is displayed
instead of the date. (the cell where this function is used is a paste-link,
and no direct data entry is done in this area).

I check the list of functions, and EDATE is no longer in the list!!!!
I check the Tool Add-ins, and the Analysis Toolpack is still selected!!!!

The only thing I have been able to do to get it to restore is to close
Excel, reopen it again, remove the Analysis toolpack and OK it, then re-add
the Analysis toolpack again.

I don't know what other functions the Analysis toolpack adds to the list to
see if other functions have also disappeared from the Excel function list,
but the EDATE is definitely missing. It's like Excel 'drops' or 'forgets'
the pack is installed???

This is the only user this happens for, we have other spreadsheets and users
using the EDATE format and the issue does not happen for them, only for this
one user, and occassionally.

Any help with this issue would be very much appreciated, I have no idea how
to fix it on the long term?

TIA, Wendy

 

[T. Valko]

I don't have any suggestions on how to fix the problem with the ATP.

You can use formulas that don't rely on ATP functions.

This one is more user friendly but longer:

A1 = some date
B1 = number of months variable

=DATE(YEAR(A1),MONTH(A1)+B1,MIN(DAY(A1),DAY(DATE(YEAR(A1),MONTH(A1)+B1+1,0))))

This one is more compact but is not real user friendly:

=MIN(DATE(YEAR(A1),MONTH(A1)+/-{n,n+/-1},DAY(A1)*{1,0}))

Where +/- depends on whether the month variable is a positive or negative
number and where n = month variable. Since the formula uses an array
constant the month variable has to be hardcoded into the formula. You could
use a range of cells but that would make it an array formula.

If the month variable is a positive number then:

=MIN(DATE(YEAR(A1),MONTH(A1)+{n,n+1},DAY(A1)*{1,0}))

For example:

A1 = some date
Month variable = 2

=MIN(DATE(YEAR(A1),MONTH(A1)+{2,3},DAY(A1)*{1,0}))

If the month variable is a negative number then:

=MIN(DATE(YEAR(A1),MONTH(A1)-{n,n-1},DAY(A1)*{1,0}))

For example:

A1 = some date
Month variable = -2

=MIN(DATE(YEAR(A1),MONTH(A1)-{2,1},DAY(A1)*{1,0}))