=EDATE and =EOMONTH #N/A Error

[andyditchfield]

I am having numerous crash problems in Excel 2007. Some of the with files were developed (and used trouble free) previously in Excel 2003 - others created directly in Excel 2007.
One of the problems appears to be the use of the EDATE and EOMONTH functions which were part of the analyst toolpack addin in 2003. This error is sporadic and causes the underlying cell value to be changed to #N/A when the file is re-opened.
Is anyone else having such issues and if so have you found a solution?
Thanks

 

[Shiva]

Hi Andy,

I am having the same issue - I am working on excel 2007 but am having to save the files as 97-2003 workbook because not all of us here are on excel 2007!!?! On re-opening these files, all EOMONTH and XIRR formulae are repalced with #N/A and I have not been able to find a solution for this. Very frustrating as this is not flagged on saving the file by the compatibility checker and like you mentioned, it is very sporadic. Saving the file as an Excel 2007 version solves this problem (so far) but unfortunately my colleagues will not be able to access this file. Will keep you posted if a solution is found.

Cheers.

Shiva

 

[DonkeyOte]

Ensure AnalysisToolPack is installed.

If distributing better to avoid EDATE/EOMONTH and use DATE function.

 

[andyditchfield]

Shiva

Your colleagues will be able to view and use your 2007 files if you download the Microsoft Office Compatability pack at the link below.

http://www.microsoft.com/downloads/...70-3ae9-4aee-8f43-c6bb74cd1466&displaylang=en

Its not perfect but its better than losing the stuff on translation.

Andy

 

[DonkeyOte]

Appreciate you guys have major work that needs to function (ie can't replace) but going forward I personally feel if you're not able to enforce VBA end-users (and thus auto install non standard yet required add-ins) you're better off sticking with standard functions

getting around EDATE/EOMONTH -- assuming A1 = TODAY() and B1 needs to be 2 months on from today

=DATE(YEAR(A1),MONTH(A1)+2,DAY(A1)

is the same as

=EDATE(A1,2)

but doesn't require analysistoolpak etc... and is safer method for distribution to other users.

 

[Peter_SSs]

=DATE(YEAR(A1),MONTH(A1)+2,DAY(A1)

is the same as

=EDATE(A1,2)

I appreciate where you are coming from here, but check these two formulas with 31 December 2007 in A1.

Note also that the OP is using Excel 2007 and that in that version, all those Add-Ins (like Analysis ToolPak) that required installation and activation in earlier versions are automatically available in 2007.

 

[DonkeyOte]

PeterSSs -- you're killing me today

I'd never seen that issue... presumably need to build is some sort of leap year handle ?
EDIT: Nope...

Yes, noted the 07 thing but figured people would search Mr E based on EOMONTH/EDATE not working for prior versions and my point would hold true... or so I thought...

 

[Shiva]

Andy,

Thanks for the link, appreciate it.........now I need to speak to IT again about sorting this out.

lasw10,

I have always used the =DATE(YEAR(A1),MONTH(A1)+2,DAY(A1) method instead of EDATE but I have not been able to use the DATE function as an alternative for EOMONTH.

If today was the 29/02/2008, then EOMONTH(29/02/2008,1) will return 31/03/2008 whereas the DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)) returns 29/03/2008 leaving out 2 days.

Regards

 

[DonkeyOte]

Yes I would have used for that:

DATE(YEAR(x),MONTH(x)+2,1)-1

But Peter has raised something I had never noticed previously re: DATE so I'm no longer the champion of DATE fn !

EDIT: presumably the fix is to go above and beyond month requirement and set DAY to 0 ?

=DATE(YEAR(x),MONTH(x)+3,0)

So if x = 31/12/07 the result of the above would be 29/02/08

 

[Peter_SSs]

PeterSSs -- you're killing me today

Yeah, it seems like that - sorry, I'll stop in a minute.

... presumably need to build is some sort of leap year handle ?
EDIT: Nope...

I guess your "Nope..." means that you realise that it is not just a leap year thing. Same thing wherever the month + 2 has fewer days than the reference month.

so I'm no longer the champion of DATE fn

Me either really, I'm just pointing out issues I have seen Barry Houdini often raise. He is about the best date formula guru on this board in my opinion. Watch his replies to Date type questions and you will learn how complicated date calculations can be - and how he can solve those complications.