Extract month from date

[Ken G.]

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.

 

[Ragdyer]

With original legal XL date in A1, try:

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

 

[ShaneDevenshire]

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.