Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 9
Default 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?
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,574
Default 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?

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 9
Default 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?

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 9
Default 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?

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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?





  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 9
Default 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?




  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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?






  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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?







  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 9
Default 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?








  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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?










Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Selling & Protecting An Excel Spreadsheet StephenAccountant Excel Discussion (Misc queries) 0 October 30th 06 05:38 AM
Shared spreadsheet changes color when opened ny another user. Why Nick DeSimone Excel Worksheet Functions 0 May 26th 06 05:12 PM
Generating Simple Reports From A Master Spreadsheet Scott1888 Excel Worksheet Functions 1 May 21st 06 10:38 AM
Spreadsheet merging problems Sam B Excel Worksheet Functions 0 September 19th 05 08:05 PM
Link Soucre changes when Spreadsheet opened from with within Outlook Cy Eze Links and Linking in Excel 0 January 6th 05 05:43 PM


All times are GMT +1. The time now is 06:24 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"