How to extract month from a date that's in Text format?

sudarshanrao

New Member
Joined
Nov 8, 2012
Messages
3
I have a column with the following values:
SubmitDate
November 8, 2012 1:37:07 PM GMT+05:30
November 8, 2012 9:32:03 AM GMT+05:30
November 8, 2012 4:32:54 AM GMT+05:30
November 7, 2012 10:28:11 PM GMT+05:30
November 7, 2012 8:36:43 PM GMT+05:30
November 7, 2012 6:33:13 AM GMT+05:30
November 1, 2012 4:14:46 AM GMT+05:30
November 1, 2012 12:46:45 AM GMT+05:30
October 31, 2012 5:43:29 PM GMT+05:30
October 31, 2012 1:06:28 PM GMT+05:30
October 25, 2012 2:06:48 AM GMT+05:30
October 25, 2012 1:08:07 AM GMT+05:30
October 22, 2012 4:02:06 PM GMT+05:30
October 19, 2012 4:16:04 AM GMT+05:30
October 19, 2012 4:12:50 AM GMT+05:30
October 12, 2012 6:19:03 PM GMT+05:30
October 12, 2012 8:30:15 AM GMT+05:30

<tbody>
</tbody>

I need another column, with just the month specified there based on above dates, so that I can manipulate the complete sheet further using a pivot. Some formatting issue isn't allowing me to use month() function, to derive the month of the date mentioned in the cell.
Using Auto-filter is too tedious, since I'd have to do it 12 times to cover each of the month. Tried recording a macro, but that too isn't helping much, since it gets specific to cells and hence isn't re-usable in another sheet with similar data.
How can I automate the task of extracting just the month from the above column?

Thanks,
Sudarshan
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

sudarshanrao

New Member
Joined
Nov 8, 2012
Messages
3
Wow, that works! But then I'd have one more request - I need to be able to sort it based on month, while I do a pivot. So is there a way to put it like -
01 Jan
02 Feb
Or any way where I can list it chronologically?

Thanks,
Sudarshan
 

Watch MrExcel Video

Forum statistics

Threads
1,118,168
Messages
5,570,618
Members
412,333
Latest member
hectamuptra
Top