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

  • Thread starter Montenegro Mick
  • Start date
M

Montenegro Mick

How do I extract month and year to a single cell from a cell tha
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 cel
A1.. That is, I don't want day or time)

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

Mic
 
R

Ron Rosenfeld

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")
 
S

Spencer101

Montenegro said:
How do I extract month and year to a single cell from a cell tha
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 cel
A1.. That is, I don't want day or time)

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

Mick

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 thei
respective month. Then when formatted as you desire they can be used i
pivots etc. and the time element is it ignored.

Hope that helps.

S
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top