edate will not resolve

  • Thread starter Thread starter Bruce
  • Start date Start date
B

Bruce

I am baffled. The cell referenced in the formula below
contains text in the format yyyymmdd. The data is coming
from a non Excel system and we are trying to minimize
change to it. We want to add a column that shows the text
date plus 39 months. The formula works, but not always.
This morning it shows #NAME?. If I double-click the
formula it will display the correct date (the cells of the
formula are formatted as a date). If I audit solve the
formula it looks like the edate() is the culprit. Why
won't the value resolve with out interaction? =EDATE
(DATEVALUE(CONCATENATE(LEFT(L2,4),"-",MID(L2,5,2),"-",RIGHT
(L2,2))),39)

Thanks
 
Have you looked in Help to see why you get a name error?
Edate is part of the ATP and if it is not installed you will get
a name error (or if you spell a function incorrectly)
 
Never heard of that one, regardless you might want to try
something else and simpler w/o using ATP

=DATE(LEFT(L2,4),MID(L2,5,2)+39,RIGHT(L2,2))
 
That's strange. What is the result of

=LEN(L2) ?

BTW, you could have a much shorter formula...

=EDATE(--TEXT(A1,"0000-00-00"),39)
 
Hi Bruce!

If:
Tools > Options
Edit Tab
Edit directly in cell checked
OK

Double clicking a cell will bring up the formula. It seems to me that
you must have double clicked and then entered. Or there is a
possibility of some event handling subroutine being triggered by the
double click.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Wednesday 23rd July: Armenia (Martyr's Day),
Egypt (Revolution Day), Equatorial Guinea (Bata's Fiesta), Fiji
(Constitution Day), Indonesia (National Children's Day), Oman
(National Day), Papua New Guinea (Remembrance Day), Syria (Egyptian
Revolution Day). Observances: Rastafarian (Birthday of Haile
Selassie), Neptunalia (Roman)
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top