EDate Unrecognized When Spreadsheet Opened Again
 

I have several spreadsheets and I made changes to a LOT of formulas using the
EDate function. I have the Analysis Tool Pack add-in checked in Excel. It
all worked fine yesterday.

Today, if I open the ss, I get the #NAME error. I have to go to Add Ins,
uncheck the Analysis Tool Pack, hit okay, then go back and re-activate it in
order to make my formula calculate. I save and close the spreadsheet, and I
open it again with the same error.

Is this a bug or does anyone know what is going on? Please help. I have
used this function in about 200 ss's in about 50 fields per ss!

These ss's are shared, and I've asked everyone who uses them to activate the
add-in. Can you help me?

EDate Unrecognized When Spreadsheet Opened Again
 

One thing to check would be to make sure calculation is set to automatic. Go
to Tools--Options--Calculations and see if it's set to automatic calculation
mode.

Dave
--
Brevity is the soul of wit.


"DallasLDY" wrote:

I have several spreadsheets and I made changes to a LOT of formulas using the
EDate function. I have the Analysis Tool Pack add-in checked in Excel. It
all worked fine yesterday.

Today, if I open the ss, I get the #NAME error. I have to go to Add Ins,
uncheck the Analysis Tool Pack, hit okay, then go back and re-activate it in
order to make my formula calculate. I save and close the spreadsheet, and I
open it again with the same error.

Is this a bug or does anyone know what is going on? Please help. I have
used this function in about 200 ss's in about 50 fields per ss!

These ss's are shared, and I've asked everyone who uses them to activate the
add-in. Can you help me?

EDate Unrecognized When Spreadsheet Opened Again
 

Yes, it is in automatic calculation mode.

"DallasLDY" wrote:

I have several spreadsheets and I made changes to a LOT of formulas using the
EDate function. I have the Analysis Tool Pack add-in checked in Excel. It
all worked fine yesterday.

Today, if I open the ss, I get the #NAME error. I have to go to Add Ins,
uncheck the Analysis Tool Pack, hit okay, then go back and re-activate it in
order to make my formula calculate. I save and close the spreadsheet, and I
open it again with the same error.

Is this a bug or does anyone know what is going on? Please help. I have
used this function in about 200 ss's in about 50 fields per ss!

These ss's are shared, and I've asked everyone who uses them to activate the
add-in. Can you help me?

EDate Unrecognized When Spreadsheet Opened Again
 

And one thing I left out. It doesn't correct the error when I re-activate
the Analysis Tool Pack. I have to highlight EDate, click the Function
button, and the click okay. Only then do I get the calculations back.

"Dave F" wrote:

One thing to check would be to make sure calculation is set to automatic. Go
to Tools--Options--Calculations and see if it's set to automatic calculation
mode.

Dave
--
Brevity is the soul of wit.


"DallasLDY" wrote:

I have several spreadsheets and I made changes to a LOT of formulas using the
EDate function. I have the Analysis Tool Pack add-in checked in Excel. It
all worked fine yesterday.

Today, if I open the ss, I get the #NAME error. I have to go to Add Ins,
uncheck the Analysis Tool Pack, hit okay, then go back and re-activate it in
order to make my formula calculate. I save and close the spreadsheet, and I
open it again with the same error.

Is this a bug or does anyone know what is going on? Please help. I have
used this function in about 200 ss's in about 50 fields per ss!

These ss's are shared, and I've asked everyone who uses them to activate the
add-in. Can you help me?

EDate Unrecognized When Spreadsheet Opened Again
 

You can use this formula as a replacement for Edate and not have to "worry"
about the ATP:

=DATE(YEAR(start_date),MONTH(start_date)+months,MI N(DAY(start_date),DAY(DATE(YEAR(start_date),MONTH( start_date)+months+1,0))))

Biff

"DallasLDY" wrote in message
...
I have several spreadsheets and I made changes to a LOT of formulas using
the
EDate function. I have the Analysis Tool Pack add-in checked in Excel.
It
all worked fine yesterday.

Today, if I open the ss, I get the #NAME error. I have to go to Add Ins,
uncheck the Analysis Tool Pack, hit okay, then go back and re-activate it
in
order to make my formula calculate. I save and close the spreadsheet, and
I
open it again with the same error.

Is this a bug or does anyone know what is going on? Please help. I have
used this function in about 200 ss's in about 50 fields per ss!

These ss's are shared, and I've asked everyone who uses them to activate
the
add-in. Can you help me?

EDate Unrecognized When Spreadsheet Opened Again
 

I'm still having a problem. I'm trying to do a conditional sum based on the
column headings, which are formatted as text(date(2006,12,1),"mmm-yy"). I
put actuals in after closing of each month, so in January 2007, I enter
December actuals. So I want this to add up Jan 06 through Dec 06. My
current formula with EDATE is:

=SUM(D2:INDEX(D2:O2,MATCH(TEXT(DATE(YEAR(EDATE($S$ 1,-1)),MONTH(EDATE($S$1,-1)),1),"mmm-yy"),$d$1:$o$1,0))))

When I substitute your function into mine (starting with Date( ), I get an
error on the second YEAR portion of your statement. In addition, if the date
is 12/1/2006, I get a result of 11/1/2007 from your function.

This is frustrating. I even tried an if statement
IF(year(s1)=1,year(s1)-1,year(s1)) but that gives me Dec-05.

Any other ideas?

Leslie

"T. Valko" wrote:

You can use this formula as a replacement for Edate and not have to "worry"
about the ATP:

=DATE(YEAR(start_date),MONTH(start_date)+months,MI N(DAY(start_date),DAY(DATE(YEAR(start_date),MONTH( start_date)+months+1,0))))

Biff

"DallasLDY" wrote in message
...
I have several spreadsheets and I made changes to a LOT of formulas using
the
EDate function. I have the Analysis Tool Pack add-in checked in Excel.
It
all worked fine yesterday.

Today, if I open the ss, I get the #NAME error. I have to go to Add Ins,
uncheck the Analysis Tool Pack, hit okay, then go back and re-activate it
in
order to make my formula calculate. I save and close the spreadsheet, and
I
open it again with the same error.

Is this a bug or does anyone know what is going on? Please help. I have
used this function in about 200 ss's in about 50 fields per ss!

These ss's are shared, and I've asked everyone who uses them to activate
the
add-in. Can you help me?

EDate Unrecognized When Spreadsheet Opened Again
 

I'm going to take a break for a few hours. I'll see what I can up with when
I return.

Biff

"DallasLDY" wrote in message
...
I'm still having a problem. I'm trying to do a conditional sum based on
the
column headings, which are formatted as text(date(2006,12,1),"mmm-yy"). I
put actuals in after closing of each month, so in January 2007, I enter
December actuals. So I want this to add up Jan 06 through Dec 06. My
current formula with EDATE is:

=SUM(D2:INDEX(D2:O2,MATCH(TEXT(DATE(YEAR(EDATE($S$ 1,-1)),MONTH(EDATE($S$1,-1)),1),"mmm-yy"),$d$1:$o$1,0))))

When I substitute your function into mine (starting with Date( ), I get an
error on the second YEAR portion of your statement. In addition, if the
date
is 12/1/2006, I get a result of 11/1/2007 from your function.

This is frustrating. I even tried an if statement
IF(year(s1)=1,year(s1)-1,year(s1)) but that gives me Dec-05.

Any other ideas?

Leslie

"T. Valko" wrote:

You can use this formula as a replacement for Edate and not have to
"worry"
about the ATP:

=DATE(YEAR(start_date),MONTH(start_date)+months,MI N(DAY(start_date),DAY(DATE(YEAR(start_date),MONTH( start_date)+months+1,0))))

Biff

"DallasLDY" wrote in message
...
I have several spreadsheets and I made changes to a LOT of formulas
using
the
EDate function. I have the Analysis Tool Pack add-in checked in Excel.
It
all worked fine yesterday.

Today, if I open the ss, I get the #NAME error. I have to go to Add
Ins,
uncheck the Analysis Tool Pack, hit okay, then go back and re-activate
it
in
order to make my formula calculate. I save and close the spreadsheet,
and
I
open it again with the same error.

Is this a bug or does anyone know what is going on? Please help. I
have
used this function in about 200 ss's in about 50 fields per ss!

These ss's are shared, and I've asked everyone who uses them to
activate
the
add-in. Can you help me?

EDate Unrecognized When Spreadsheet Opened Again
 

=SUM(D2:INDEX(D2:O2,MATCH(TEXT(DATE(YEAR(EDATE($S$ 1,-1)),MONTH(EDATE($S$1,-1)),1),"mmm-yy"),$d$1:$o$1,0))))

Your formula works just fine for me.

However, it can be reduced to:

=SUM(D2:INDEX(D2:O2,MATCH(TEXT(S1-1,"mmm-yy"),$D$1:$O$1,0)))

I'm assuming that cell S1 contains a date in January 2007? I used 1/1/2007
in cell S1. If cell S1 may contain any date in January 2007, like, say,
=TODAY(), then use:

=SUM(D2:INDEX(D2:O2,MATCH(TEXT($S$1-DAY($S$1),"mmm-yy"),$D$1:$O$1,0)))

Biff

"T. Valko" wrote in message
...
I'm going to take a break for a few hours. I'll see what I can up with
when I return.

Biff

"DallasLDY" wrote in message
...
I'm still having a problem. I'm trying to do a conditional sum based on
the
column headings, which are formatted as text(date(2006,12,1),"mmm-yy").
I
put actuals in after closing of each month, so in January 2007, I enter
December actuals. So I want this to add up Jan 06 through Dec 06. My
current formula with EDATE is:

=SUM(D2:INDEX(D2:O2,MATCH(TEXT(DATE(YEAR(EDATE($S$ 1,-1)),MONTH(EDATE($S$1,-1)),1),"mmm-yy"),$d$1:$o$1,0))))

When I substitute your function into mine (starting with Date( ), I get
an
error on the second YEAR portion of your statement. In addition, if the
date
is 12/1/2006, I get a result of 11/1/2007 from your function.

This is frustrating. I even tried an if statement
IF(year(s1)=1,year(s1)-1,year(s1)) but that gives me Dec-05.

Any other ideas?

Leslie

"T. Valko" wrote:

You can use this formula as a replacement for Edate and not have to
"worry"
about the ATP:

=DATE(YEAR(start_date),MONTH(start_date)+months,MI N(DAY(start_date),DAY(DATE(YEAR(start_date),MONTH( start_date)+months+1,0))))

Biff

"DallasLDY" wrote in message
...
I have several spreadsheets and I made changes to a LOT of formulas
using
the
EDate function. I have the Analysis Tool Pack add-in checked in
Excel.
It
all worked fine yesterday.

Today, if I open the ss, I get the #NAME error. I have to go to Add
Ins,
uncheck the Analysis Tool Pack, hit okay, then go back and re-activate
it
in
order to make my formula calculate. I save and close the spreadsheet,
and
I
open it again with the same error.

Is this a bug or does anyone know what is going on? Please help. I
have
used this function in about 200 ss's in about 50 fields per ss!

These ss's are shared, and I've asked everyone who uses them to
activate
the
add-in. Can you help me?

EDate Unrecognized When Spreadsheet Opened Again
 

Thank you, Biff. You solved my problem. Although today EDate is working
again. Mystery.

Anyway, S1 contains TEXT(TODAY(),"mmm-yy") for the most part. Then, when
that period has passed but I want to preserve the historical spreadsheet, I
change it to TEXT(DATE(2007,1,1),"mmm-yy").

With the information you provided below, I'm seeing what my problem is. I
appreciate all your help.

"T. Valko" wrote:

=SUM(D2:INDEX(D2:O2,MATCH(TEXT(DATE(YEAR(EDATE($S$ 1,-1)),MONTH(EDATE($S$1,-1)),1),"mmm-yy"),$d$1:$o$1,0))))

Your formula works just fine for me.

However, it can be reduced to:

=SUM(D2:INDEX(D2:O2,MATCH(TEXT(S1-1,"mmm-yy"),$D$1:$O$1,0)))

I'm assuming that cell S1 contains a date in January 2007? I used 1/1/2007
in cell S1. If cell S1 may contain any date in January 2007, like, say,
=TODAY(), then use:

=SUM(D2:INDEX(D2:O2,MATCH(TEXT($S$1-DAY($S$1),"mmm-yy"),$D$1:$O$1,0)))

Biff

"T. Valko" wrote in message
...
I'm going to take a break for a few hours. I'll see what I can up with
when I return.

Biff

"DallasLDY" wrote in message
...
I'm still having a problem. I'm trying to do a conditional sum based on
the
column headings, which are formatted as text(date(2006,12,1),"mmm-yy").
I
put actuals in after closing of each month, so in January 2007, I enter
December actuals. So I want this to add up Jan 06 through Dec 06. My
current formula with EDATE is:

=SUM(D2:INDEX(D2:O2,MATCH(TEXT(DATE(YEAR(EDATE($S$ 1,-1)),MONTH(EDATE($S$1,-1)),1),"mmm-yy"),$d$1:$o$1,0))))

When I substitute your function into mine (starting with Date( ), I get
an
error on the second YEAR portion of your statement. In addition, if the
date
is 12/1/2006, I get a result of 11/1/2007 from your function.

This is frustrating. I even tried an if statement
IF(year(s1)=1,year(s1)-1,year(s1)) but that gives me Dec-05.

Any other ideas?

Leslie

"T. Valko" wrote:

You can use this formula as a replacement for Edate and not have to
"worry"
about the ATP:

=DATE(YEAR(start_date),MONTH(start_date)+months,MI N(DAY(start_date),DAY(DATE(YEAR(start_date),MONTH( start_date)+months+1,0))))

Biff

"DallasLDY" wrote in message
...
I have several spreadsheets and I made changes to a LOT of formulas
using
the
EDate function. I have the Analysis Tool Pack add-in checked in
Excel.
It
all worked fine yesterday.

Today, if I open the ss, I get the #NAME error. I have to go to Add
Ins,
uncheck the Analysis Tool Pack, hit okay, then go back and re-activate
it
in
order to make my formula calculate. I save and close the spreadsheet,
and
I
open it again with the same error.

Is this a bug or does anyone know what is going on? Please help. I
have
used this function in about 200 ss's in about 50 fields per ss!

These ss's are shared, and I've asked everyone who uses them to
activate
the
add-in. Can you help me?

EDate Unrecognized When Spreadsheet Opened Again
 

You're welcome. Thanks for the feedback!

Biff

"DallasLDY" wrote in message
...
Thank you, Biff. You solved my problem. Although today EDate is working
again. Mystery.

Anyway, S1 contains TEXT(TODAY(),"mmm-yy") for the most part. Then, when
that period has passed but I want to preserve the historical spreadsheet,
I
change it to TEXT(DATE(2007,1,1),"mmm-yy").

With the information you provided below, I'm seeing what my problem is. I
appreciate all your help.

"T. Valko" wrote:

=SUM(D2:INDEX(D2:O2,MATCH(TEXT(DATE(YEAR(EDATE($S$ 1,-1)),MONTH(EDATE($S$1,-1)),1),"mmm-yy"),$d$1:$o$1,0))))

Your formula works just fine for me.

However, it can be reduced to:

=SUM(D2:INDEX(D2:O2,MATCH(TEXT(S1-1,"mmm-yy"),$D$1:$O$1,0)))

I'm assuming that cell S1 contains a date in January 2007? I used
1/1/2007
in cell S1. If cell S1 may contain any date in January 2007, like, say,
=TODAY(), then use:

=SUM(D2:INDEX(D2:O2,MATCH(TEXT($S$1-DAY($S$1),"mmm-yy"),$D$1:$O$1,0)))

Biff

"T. Valko" wrote in message
...
I'm going to take a break for a few hours. I'll see what I can up with
when I return.

Biff

"DallasLDY" wrote in message
...
I'm still having a problem. I'm trying to do a conditional sum based
on
the
column headings, which are formatted as
text(date(2006,12,1),"mmm-yy").
I
put actuals in after closing of each month, so in January 2007, I
enter
December actuals. So I want this to add up Jan 06 through Dec 06. My
current formula with EDATE is:

=SUM(D2:INDEX(D2:O2,MATCH(TEXT(DATE(YEAR(EDATE($S$ 1,-1)),MONTH(EDATE($S$1,-1)),1),"mmm-yy"),$d$1:$o$1,0))))

When I substitute your function into mine (starting with Date( ), I
get
an
error on the second YEAR portion of your statement. In addition, if
the
date
is 12/1/2006, I get a result of 11/1/2007 from your function.

This is frustrating. I even tried an if statement
IF(year(s1)=1,year(s1)-1,year(s1)) but that gives me Dec-05.

Any other ideas?

Leslie

"T. Valko" wrote:

You can use this formula as a replacement for Edate and not have to
"worry"
about the ATP:

=DATE(YEAR(start_date),MONTH(start_date)+months,MI N(DAY(start_date),DAY(DATE(YEAR(start_date),MONTH( start_date)+months+1,0))))

Biff

"DallasLDY" wrote in message
...
I have several spreadsheets and I made changes to a LOT of formulas
using
the
EDate function. I have the Analysis Tool Pack add-in checked in
Excel.
It
all worked fine yesterday.

Today, if I open the ss, I get the #NAME error. I have to go to
Add
Ins,
uncheck the Analysis Tool Pack, hit okay, then go back and
re-activate
it
in
order to make my formula calculate. I save and close the
spreadsheet,
and
I
open it again with the same error.

Is this a bug or does anyone know what is going on? Please help.
I
have
used this function in about 200 ss's in about 50 fields per ss!

These ss's are shared, and I've asked everyone who uses them to
activate
the
add-in. Can you help me?