Extract Month and year from Cell containing date, month, year & time
 

How do I extract month and year to a single cell from a cell that contains dd/mm/yyyy hh:mm:ss?

By way of example:
Cell A2 contains, 4/01/2011 11:25:30 AM

Cell B2 to contain, Jan 2011
(However, it is not to carry over any of the non required data from cell A1.. That is, I don't want day or time)

Outcome is a Pivot Table that tracks order fulfillment - Orders submitted in a month and completions for the month and months that follow.

Mick

Extract Month and year from Cell containing date, month, year & time
 

On Tue, 1 May 2012 23:52:01 +0000, Montenegro Mick wrote:

How do I extract month and year to a single cell from a cell that
contains dd/mm/yyyy hh:mm:ss?

By way of example:
Cell A2 contains, 4/01/2011 11:25:30 AM

Cell B2 to contain, Jan 2011
(However, it is not to carry over any of the non required data from cell
A1.. That is, I don't want day or time)

Outcome is a Pivot Table that tracks order fulfillment - Orders
submitted in a month and completions for the month and months that
follow.

There is no need to do that if your dates are going to be in the rows of the Pivot Table. Once you set up your Pivot table, right-click in the rows area, and select to group by months (or months and years if that's what you want).

In any event, if you want the value in B2 to be a date, it MUST contain the day. Otherwise you could have it as a text string, using something like:
B2: =text(a2, "mmm yyyy")

Hi Mick,

In B2 enter =EOMONTH(A2,-1)+1 then format it to mmm/yyyy

What is does is force all the dates to be the first day of their respective month. Then when formatted as you desire they can be used in pivots etc. and the time element is it ignored.

Hope that helps.

S.