Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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") |
#3
|
|||
|
|||
Quote:
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you extract day/time/month/year information | Excel Worksheet Functions | |||
count month when date is in day/month/year format | Excel Worksheet Functions | |||
Tell me which "season" (Month/Day through Month/Day) a date(Month/Day/Year) falls in (any year)??? | Excel Discussion (Misc queries) | |||
Sort month/date/year data using month and date only | Excel Discussion (Misc queries) | |||
trying to get day/month/year froamt while user enters year only | New Users to Excel |