software

When working with the DATE function to subtract one month from a given dat,
using the following string:
=TEXT(DATE(YEAR(Report_Date),MONTH(Report_Date)-1,DAY(Report_Date)),quot;mmm yyquot;)
amp; quot; YTD Actualquot;, the formula doesn't fully take account of different length
months.

If I use the date 31/10/06 the prior month returned is still October. To
correct this I have to use 30/10/6 instead, then check each instance of the
formula to ensure all report headings are correct.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the quot;I
Agreequot; button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click quot;I Agreequot; in the message pane.

www.microsoft.com/office/comm...et.f unctions


Try

=TEXT(Report_Date-DAY(Report_Date),quot;mmm yyquot;)...or if you have Analysis ToolPak installed you can use

=TEXT(EDATE(Report_Date,-1),quot;mmm-yyquot;) or
=TEXT(EOMONTH(Report_Date,-1),quot;mmm-yyquot;)--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=507133Try

=TEXT(MIN(DATE(YEAR(Report_Date),MONTH(Report_Date )-{1,0},(DAY(Report_Date)*
{1,0}))),quot;mmm yyquot;)amp;quot; YTD Actualquot;

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Grahamquot; gt; wrote in message
...
gt; When working with the DATE function to subtract one month from a given
dat,
gt; using the following string:
gt; =TEXT(DATE(YEAR(Report_Date),MONTH(Report_Date)-1,DAY(Report_Date)),quot;mmm
yyquot;)
gt; amp; quot; YTD Actualquot;, the formula doesn't fully take account of different
length
gt; months.
gt;
gt; If I use the date 31/10/06 the prior month returned is still October. To
gt; correct this I have to use 30/10/6 instead, then check each instance of
the
gt; formula to ensure all report headings are correct.
gt;
gt; ----------------
gt; This post is a suggestion for Microsoft, and Microsoft responds to the
gt; suggestions with the most votes. To vote for this suggestion, click the quot;I
gt; Agreequot; button in the message pane. If you do not see the button, follow
this
gt; link to open the suggestion in the Microsoft Web-based Newsreader and then
gt; click quot;I Agreequot; in the message pane.
gt;
gt;
www.microsoft.com/office/comm...et.f unctions
On Wed, 1 Feb 2006 01:31:26 -0800, Graham gt;
wrote:

gt;When working with the DATE function to subtract one month from a given dat,
gt;using the following string:
gt;=TEXT(DATE(YEAR(Report_Date),MONTH(Report_Date)-1,DAY(Report_Date)),quot;mmm yyquot;)
gt;amp; quot; YTD Actualquot;, the formula doesn't fully take account of different length
gt;months.
gt;
gt;If I use the date 31/10/06 the prior month returned is still October. To
gt;correct this I have to use 30/10/6 instead, then check each instance of the
gt;formula to ensure all report headings are correct.
gt;
gt;----------------
gt;This post is a suggestion for Microsoft, and Microsoft responds to the
gt;suggestions with the most votes. To vote for this suggestion, click the quot;I
gt;Agreequot; button in the message pane. If you do not see the button, follow this
gt;link to open the suggestion in the Microsoft Web-based Newsreader and then
gt;click quot;I Agreequot; in the message pane.
gt;
----------------------

Microsoft solved this problem a long time ago by distributing the Analysis Tool
Pak. I have heard rumors that it will be an integral part of Excel12 and not
even require that you navigate to Tools/Add-ins and check the already appearing
option.

I suspect that Microsoft does not respond to many suggestions because, if they
are like this and similar messages frequently posted here, there is so much
quot;noisequot; embedded with the useful suggestions that even useful one's may get
ignored or overlooked.

Rather than appending a message with useless boiler-plate, it would be best to
first find out if the so-called suggestion has already been fixed.
--ron