EDATE returns #NA Error when Excel opened again

[SalD]

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

 

[Ron Rosenfeld]

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

Post your formula and it's precedents.

I'll guess there is a problem with your data.

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

 

[SalD]

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]

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,

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?

#NA usually means that the function is lacking some information. Does the #NA
go away if you force a recalculation? IF so, do you have calculation set to
automatic?
--ron

 

[SalD]

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?

 

[T. Valko]

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.

 

[Ron Rosenfeld]

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?

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