I have a cell with the date in the format of November 1, 2008 and I want to
read that cell and put the previous month and current year (unless the month
displayed in the cell is January, in which case I want the new cell to show
December of the previous year) into a text string in another cell. I always
struggle manipulating dates in Excel and as usual, I can't get the correct
formula. Please help.

With original legal XL date in A1, try:

=TEXT(DATE(YEAR(A1),MONTH(A1)-1,1),"mmmm/yyyy")

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ken G." wrote in message
...
I have a cell with the date in the format of November 1, 2008 and I want
to
read that cell and put the previous month and current year (unless the
month
displayed in the cell is January, in which case I want the new cell to
show
December of the previous year) into a text string in another cell. I
always
struggle manipulating dates in Excel and as usual, I can't get the correct
formula. Please help.

Hi,

First, for the prior month

=EDATE(A1,-1)

Where the original date is in A1. This gives you the same day of the month,
but in the prior month, regardless of the year. This is an analysis toolpak
function, so if you are using Excel 2003 or earlier choose Tools, Add-ins,
and check the box beside Analysis ToolPak

Second, to use the result in a text string

a. Where the text string is typed into the formula:

="The date is: "&TEXT(EDATE(A1,-1),"MMMM")

The MMMM is just the format codes, from the Format Cells dialog box, that
cause the format to display the month aa a fully spelled name.

b. Where the text string is in another cell:

=B1&" - "&TEXT(EDATE(A1,-1),"m/d/yy")

In this case your date would be displayed as 9/4/08. Again just the format
codes for that display. Notice that caps don't make any difference.

If this helps, please click the Yes button.


--
Thanks,
Shane Devenshire


"Ken G." wrote:

I have a cell with the date in the format of November 1, 2008 and I want to
read that cell and put the previous month and current year (unless the month
displayed in the cell is January, in which case I want the new cell to show
December of the previous year) into a text string in another cell. I always
struggle manipulating dates in Excel and as usual, I can't get the correct
formula. Please help.