Converting text-formatted dates into months

**pondorpool** · 03-09-2010, 12:51 PM

I have used basic formulas for years but I can't figure out how to do this one at all...

I have a report that is almost 2000 lines long and has one column that has invoice dates but they are in a 'text' format meaning that I can't use the formating tool to convert them into any other true date format (the original data was pulled from an SAP system)

What I need to do is extract the month from this column so that I have a column just indicating the month of the transaction

Sample
12/23/2009 --> December
02/12/2009 --> February
12/02/2009 --> December
05/10/2009 --> May
10/05/2009 --> October

I found some very very basic forumlas but they don't work when you have months with the zero in front (01, 02, 03, etc) and it involves mutlitple forumlas over mutliple columns that you then merge all the results together... not a very good way to do this.

Any help would be greatly appreciated!

**NBVC** · 03-09-2010, 12:53 PM

Does this work?

=TEXT(A1,"mmmm")

where A1 has date entry.

**darkyam** · 03-09-2010, 01:31 PM

Option B is =A1 and then use a custom format of mmmm.

**pondorpool** · 03-09-2010, 02:03 PM

nope... neither of those options work. As I mentioned - I can't seem to do anything with formating of the date field in question. When I try the formulas suggested it just returns with the exact same value... ie. A1 12/31/2009 --> A2 =TEXT(A1,"mmmm") --> A3 still shows 12/31/2009

The way I had started to to this was using an IF statement =IF(ISNUMBER(SEARCH("12/",A3)),"December"," ") which worked for Oct, Nov & Dec but not the other 9 months that showed up as 01, 02,03 in the date field. Besides it meant having to create 12 columns and then merging them to get a complete list (as I said, I understand basic formulas and don't know how to nest functions to get a 12 if then type statement)...

Any other suggestions?

**darkyam** · 03-09-2010, 02:05 PM

Try putting 1 in a cell, copying that cell, then highlighting column A, Paste Special, multiply, Ok. That should force all the dates into being dates.

**NBVC** · 03-09-2010, 02:06 PM

How about?:

=TEXT(CLEAN(A1),"mmmm")

**pondorpool** · 03-09-2010, 02:37 PM

This just gets weirder and weirder...

NBVC - your suggestion works kind of... it converted some of the rows but it used the middle value of the date (ie 06/02/2009) so the result was February when it should have been June. For all the rows where the day was greater than 12 then it didn't do anything and simply put the same date in again (06/02/2009). Those new dates are still just text and can't be reformated, converted, etc

darkyam - your suggestion works kind of... same issue as with NBVC's solution. For some reason when I paste special / multiply it changes the same rows that have a day value of 12 and under but it does nothing for the remaining fields (the ones that it does convert to a number can then be formatted to mmmm to show the month

Here is sample of the data and what happens using each suggestion

Date Column NBVC darkyam
12/31/2009 12/31/2009 12/31/2009
06/02/2009 February 39850
07/13/2009 07/13/2009 07/13/2009
11/07/2007 July 39274
12/18/2007 12/18/2007 12/18/2007
06/23/2009 06/23/2009 06/23/2009
06/24/2009 06/24/2009 06/24/2009
08/18/2009 08/18/2009 08/18/2009
09/11/2009 November 40126
08/18/2009 08/18/2009 08/18/2009
09/21/2009 09/21/2009 09/21/2009

I checked again and the entire column is formated as 'General'...

**NBVC** · 03-09-2010, 02:59 PM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

**darkyam** · 03-09-2010, 04:14 PM

=Date(Right(A1,4),Left(A1,2),Mid(A1,4,2)), formatted as mmmm or wrapped in a Text formula as NBVC suggests.

**pondorpool** · 03-09-2010, 06:02 PM

Here is a sample worksheet that has 2 of the columns that come standard in the report from SAP and then the other columns that I am trying to calculate (I've included notes explaining what I'm trying to do...)

Thanks

**NBVC** · 03-09-2010, 06:07 PM

My original formula worked right off the bat for me.. but that might be because I located in North America and our date system is different...

Try this... Select column H and go to Data|Text to columns and skip to the 3rd Dialogue box, select Date from the column data format area and then choose MDY in drop down.. click Finish..

Does that fix it..? If not, try with DMY...

**pondorpool** · 03-09-2010, 09:43 PM

Thank you NBVC!!

It worked after selecting the column and doing the Date|Text conversion using MDY

The strange thing is I'm just down the road from you in Toronto so I can't figure out why it worked right away on your system and not on mine? My excel is set up to use the default 1900 date system so I'm trying to think about what else could be different?

Anyways - thanks again!