I have experienced the same error. In my case, when I reopen the spreadsheet, the edate formula is replaced by =#NA in the cell. The only workaround I have found is to create the dates using YEAR, MONTH and DAY formulas, but that seems pretty pointless given that the edate function has been around for years. Anybody have an idea why this may be happening?



Robert Sonderegger wrote:

Confirmation of EDATE and YEARFRAC problem under Excel 2007
14-Sep-09

I'd like to confirm the observations in this thread but -- alas -- have no solutions. Here is the exact description. I have used YEARFRAC and EDATE for many years in hundreds of spreadsheets with Ex

Previous Posts In This Thread:

On 20 February 2008 08:27
Sal wrote:

EDATE returns #NA Error when Excel opened again
Hi,

I use the EDATE function a lot. My setting are set to 'Automatic
Calculation' and Analysis toll pack is also added in. However, when i open
files with the EDATE function used, the relevant cell returns the #NA result.
Any ideas why this might happen and how I can get a permanenet solution?

Thanks

On 20 February 2008 09:05
Ron Rosenfeld wrote:

EDATE returns #NA Error when Excel opened again
wrote:


Post your formula and it is precedents.

I will guess there is a problem with your data.

Also, what version of Excel???
--ron

On 20 February 2008 09:42
Sal wrote:

Cell A1 = 1-Apr-08Cell A2 = 36Cell A3 = edate(A1,A2)I get the result as
Cell A1 = 1-Apr-08
Cell A2 = 36
Cell A3 = edate(A1,A2)

I get the result as 1-Apr-11 when I enter the formula. All calculations
dependent on the date are also performed perfectly well. It's only when I
close and re-open the file that I lose the date and get a #NA result.

I am using Excel 2007.

Thanks,

"Ron Rosenfeld" wrote:

On 20 February 2008 10:08
Ron Rosenfeld wrote:

EDATE returns #NA Error when Excel opened again
On Wed, 20 Feb 2008 06:42:00 -0800, SalD
wrote:


Are the values in A1 and/or A2 the result of formulas, or are they entered
directly as values. If formulas, what are they?

Does either A1 or A2 also show #NA?

If you click on A3 while it is showing #NA, do you get a button with "Trace
Error" showing in the click menu? If so, what does that show?

go away if you force a recalculation? IF so, do you have calculation set to
automatic?
--ron

On 20 February 2008 23:47
Sal wrote:

Cells A1 and A2 are entered as values and not formulas.
Cells A1 and A2 are entered as values and not formulas. And both these cells
do not show a #NA result.

The Trace Error button for cell A3 gives the message "A value is not
available to the formula or function"

My calculations setting are set to Automatic. And the #NA does not go away
if I force a recalculation.

Does Excel 2007 have any specific issues in dealing with date functions?

"Ron Rosenfeld" wrote:

On 21 February 2008 00:44
T. Valko wrote:

Works fine for me in Excel 2007.
Works fine for me in Excel 2007.

I have seen a few posts describing this behavior in Excel 2007 that uses
functions which were previously only available through the Analysis ToolPak
addin. However, I haven't been able to duplicate any of those problems.

--
Biff
Microsoft Excel MVP


"SalD" wrote in message
...

On 21 February 2008 06:46
Ron Rosenfeld wrote:

EDATE returns #NA Error when Excel opened again
On Wed, 20 Feb 2008 20:47:00 -0800, SalD
wrote:


Unfortunately, I do not have Excel 2007 and I am not able to replicate your
problem in 2003. For some reason, it seems as if the EDATE function in A3 is
not picking up the values in A1 or A2 when you first open the workbook.

Other than trying this in a new workbook, where these entries are the only
entries, and opening a case with Microsoft support, I cannot think of anything
else. (This NOT Microsoft support -- only volunteers here).

I did a quick Google search and did not find anything relevant to this problem.
--ron

On 10 January 2009 08:01
richard flavell wrote:

EDATE returns #NA Error when Excel opened again
I also run Excel 2007 under Vista. I also occasionally get exactly the same error, returning #N/A, when a valid function is reopened.
Have you found any solution, as I have not??

On 14 September 2009 12:09
Robert Sonderegger wrote:

Confirmation of EDATE and YEARFRAC problem under Excel 2007
I'd like to confirm the observations in this thread but -- alas -- have no solutions. Here is the exact description. I have used YEARFRAC and EDATE for many years in hundreds of spreadsheets with Ex

EggHeadCafe - Software Developer Portal of Choice
Create Function in SQL Server
http://www.eggheadcafe.com/tutorials...in-sql-se.aspx