No Analysis Tool Pack=EDATE formula not working! HELP!!

[Danny Boy]

The formula below works just fine FOR ME. However, one of our work computers
does not have the analysis tool pack downloaded, and unfortunately the Office
XP Home Edition CD that is needed to download the tool pack seems to have
gone missing.

When I place this same formula on the computer missing the "analysis tool
pack" I get the "n/a!" error. On my computer the formula works just fine.

My quesiton is this? Is there a way of altering the formula below so that it
can work properly for the computer missing the analysis tool pack? I believe
that the piece of the formula which is causing the problem (and needing the
analysis tool pack), is the EDATE portion of the formula. So if there is a
way of doing the same thing, but not using the EDATE function, I would
appreciate some feedback.

=IF(M4="","",IF(P4="","",IF(TODAY()>EDATE(M4,3),"Notify Referral Source-Exit
Not completed","")))

Thank you in advance!

Dan

 

[Mike H]

Try this

=IF(M4="","",IF(P4="","",IF(TODAY()>DATE(YEAR(M4),MONTH(M4)+3,DAY(M4)),"Notify Referral Source-Exit Not completed","")))

Mike

 

[T. Valko]

DATE(YEAR(M4),MONTH(M4)+3,DAY(M4))

That doesn't exactly emulate the EDATE function.

M4 = 11/30/2008

EDATE(M4,3) = 2/28/2009
DATE(YEAR(M4),MONTH(M4)+3,DAY(M4)) = 3/2/2009

Try this:

MIN(DATE(YEAR(M4),MONTH(M4)+{n,n+1},DAY(M4)*{1,0}))

Where n = number of months. For 3 months:

MIN(DATE(YEAR(M4),MONTH(M4)+{3,4},DAY(M4)*{1,0}))

Returns 2/28/2009

 

[T. Valko]

See my reply to David wrt an EDATE replacement formula. Using the
MONTH(...)+n method doesn't exactly emulate the EDATE function.

 

[Shane Devenshire]

Hi,

1. the standard install automatically puts the ATP on your hard drive, so
in most cases you can just attach it by choosing Tools, Add-ins.
2. The absence of the ATP I get a VALUE error not a N/A error. So I'm not
sure what problem you really are having.
3. The default location on a regular installation is in the folder
C:\Program Files\Microsoft Office\Office11\Library\Analysis

There will be 4 files in the Analysis folder.

ANALYS32.XLL
FUNCRES.XLA
ATPVBAEN.XLA
PROCDB.XLA

You can copy these from your machine to the other users machines.

4. If you still what the formula:

=IF(AND(M4<>"",P4<>"",TODAY()>MIN(DATE(YEAR(M4),MONTH(M4)+{3,4},DAY(M4)*{1,0}))),"Notify Referral Source-Exit Not completed","")

 

[Rick Rothstein]

I think this formula works as a substitute for the EDATE function...

=MIN(DATE(YEAR(A1),MONTH(A1)+N,DAY(A1)),DATE(YEAR(A1),MONTH(A1)+1+N,0))

where N is the number of months to be added.

 

[T. Valko]

I think this formula works as a substitute for the EDATE function...

=MIN(DATE(YEAR(A1),MONTH(A1)+N,DAY(A1)),DATE(YEAR(A1),MONTH(A1)+1+N,0))

Or, you can write it like this:

=MIN(DATE(YEAR(A1),MONTH(A1)+{n,n+1},DAY(A1)*{1,0}))

 

[Rick Rothstein]

Ah yes... I should have read **all** of your previous posting, not just the
top half of it, huh?<g>

--
Rick (MVP - Excel)

I think this formula works as a substitute for the EDATE function...
=MIN(DATE(YEAR(A1),MONTH(A1)+N,DAY(A1)),DATE(YEAR(A1),MONTH(A1)+1+N,0))

Or, you can write it like this:

=MIN(DATE(YEAR(A1),MONTH(A1)+{n,n+1},DAY(A1)*{1,0}))


--
Biff
Microsoft Excel MVP

I think this formula works as a substitute for the EDATE function...

=MIN(DATE(YEAR(A1),MONTH(A1)+N,DAY(A1)),DATE(YEAR(A1),MONTH(A1)+1+N,0))

where N is the number of months to be added.

--
Rick (MVP - Excel)

DATE(YEAR(M4),MONTH(M4)+3,DAY(M4))

That doesn't exactly emulate the EDATE function.

M4 = 11/30/2008

EDATE(M4,3) = 2/28/2009
DATE(YEAR(M4),MONTH(M4)+3,DAY(M4)) = 3/2/2009

Try this:

MIN(DATE(YEAR(M4),MONTH(M4)+{n,n+1},DAY(M4)*{1,0}))

Where n = number of months. For 3 months:

MIN(DATE(YEAR(M4),MONTH(M4)+{3,4},DAY(M4)*{1,0}))

Returns 2/28/2009

--
Biff
Microsoft Excel MVP


"David Biddulph" <groups [at] biddulph.org.uk> wrote in message
=IF(M4="","",IF(P4="","",IF(TODAY()>DATE(YEAR(M4),MONTH(M4)+3,DAY(M4)),"Notify
Referral Source-Exit Not completed","")))
--
David Biddulph

Danny Boy wrote:
The formula below works just fine FOR ME. However, one of our work
computers does not have the analysis tool pack downloaded, and
unfortunately the Office XP Home Edition CD that is needed to
download the tool pack seems to have gone missing.

When I place this same formula on the computer missing the "analysis
tool pack" I get the "n/a!" error. On my computer the formula works
just fine.

My quesiton is this? Is there a way of altering the formula below so
that it can work properly for the computer missing the analysis tool
pack? I believe that the piece of the formula which is causing the
problem (and needing the analysis tool pack), is the EDATE portion of
the formula. So if there is a way of doing the same thing, but not
using the EDATE function, I would appreciate some feedback.

=IF(M4="","",IF(P4="","",IF(TODAY()>EDATE(M4,3),"Notify Referral
Source-Exit Not completed","")))

Thank you in advance!

Dan

 

[Danny Boy]

THANKS EVERYONE FOR THE HELP AND HAPPY NEW YEAR!!!!!!