Date + n months

T

TCEBob

Excel 97, win xp.

I have a column for date (as in Med Refill Date) and another for Refills
Remaining. The date column is formatted as 04/07/04 but is not a string.
After shuffling through the available functions I can't find one to add
RR months to the date and show the formatted date. Tried Edate but it
wants a string.

rs
 
A

Andy B

Hi

According to Excel (mine's 2000) EDATE requires a 'serial date number that
represents the start date' for the Start_date and a number of months.
It doesn't mention wanting a string.
 
T

TCEBob

Here's the formula: edate(E8,F8). E8 is a formatted date and F8 is an
integer.
Formula returns #NAME?.

rs
 
R

Ron Rosenfeld

Excel 97, win xp.

I have a column for date (as in Med Refill Date) and another for Refills
Remaining. The date column is formatted as 04/07/04 but is not a string.
After shuffling through the available functions I can't find one to add
RR months to the date and show the formatted date. Tried Edate but it
wants a string.

rs
Click to expand...

EDATE does NOT take strings for arguments.

Probably your date is a string. Just because the FORMAT is a date, does not
mean that the entry cannot be a string. A simple way of checking your entry
would be, if your entry is in A1, do: =ISTEXT(A1) If this formula returns
TRUE, then you know that A1 is a string, and not a date.


--ron
 
T

TCEBob

Nope. ISTEXT returns FALSE. I also formatted the dates as "general" but
EDATE still errors out. I realize that the function does not use strings
but the error #NAME is a string error, no?

rs

 
P

Peo Sjoblom

It returns a name error because you haven't installed ATP, do tools>add-ins,
check ATP and keep
the CD handy. It comes with office/excel but it does not install by
default.. EDATE is part of the
Analysis ToolPak

If you can't install it use

=DATE(YEAR(E8),MONTH(E8)+F8,DAY(E8))

--

Regards,

Peo Sjoblom


TCEBob said:
Nope. ISTEXT returns FALSE. I also formatted the dates as "general" but
EDATE still errors out. I realize that the function does not use strings
but the error #NAME is a string error, no?

rs
Click to expand...
 
R

Ron Rosenfeld

Nope. ISTEXT returns FALSE. I also formatted the dates as "general" but
EDATE still errors out. I realize that the function does not use strings
but the error #NAME is a string error, no?
Click to expand...

No, the error #NAME means you don't have the Analysis Toolpak installed. This
is pretty clear if you select HELP for EDATE:


If this function is not available, and returns the #NAME? error, install and
load the Analysis ToolPak add-in.

How?

On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak box, and then click
OK.
If necessary, follow the instructions in the setup program.


--ron
 
T

TCEBob

Ah. Well, your help is more helpful than my help.

"If this function is not available, run the Setup program to install the
Analysis ToolPak. After you install the Analysis ToolPak, you must
enable it by using the Add-Ins command on the Tools menu."

Thing is, by all appearances the function is available -- until I get
the error. It was loaded but not checked in the Tools/Add-ins list.
Checked it and it works fine.

Thanks for the alert.


 
R

Ron Rosenfeld

Thing is, by all appearances the function is available -- until I get
the error. It was loaded but not checked in the Tools/Add-ins list.
Checked it and it works fine.
Click to expand...

Sometimes HELP assumes knowledge that many folk don't have, and Microsoft
sometimes uses their own brand of English :).

In this case, the NAME error means you've used a NAME (in this case EDATE) that
XL doesn't recognize. It's not really a NAME, but since XL didn't recognize
it, it thinks it's an undefined NAME.

Glad you got things working.

Best,

--ron
 
Top