Formula to Extract Month and year

[howard]

I have the following text in cell F10 "11/02P YTD" The 11 represents the year (2011) and 02 represent the month (Feb)

I would like a formula in F20, that will extract the month and year to appear as Feb 2011 from cell F10

Your assistance will be most appreciated

 

[Andrew Poulsom]

Try:

=(20&LEFT(F10,5)&"/01")+0

formatted as mmm yyyy.

 

[howard]

Hi Andrew

Thanks for the reply. I have used your formula and formatted the cell as mmm yyyy and I get #value!

It would be appreciated if you could assist

 

[Andrew Poulsom]

My formula worked when I tried it. It returns the string 2011/02/01 which is coerced into a date by adding zero. This also worked:

=TEXT((20&LEFT(F10,5)&"/01"),"yyyy/mm/dd")+0

 

[howard]

Hi Andrew

I found where the problem was. This was an old Lotus workbook that was converted into Excel several years ago. I unchecked the Lotus compatibility settings and the formula works perfectly

I only need one small amendment. I need the month to change each time "11/02P YTD, the text 02 changes for eg when the text in F10 changes to 11/03 then the result of the formula must read March

 

[Andrew Poulsom]

I only need one small amendment. I need the month to change each time "11/02P YTD, the text 02 changes for eg when the text in F10 changes to 11/03 then the result of the formula must read March

That will happen automatically with the formula I posted.

 

[howard]

Hi Andrew

The result of the current formula gives me Jan 2011, it should give me Feb 2011, based on the text in cell F10 "11/02P YTD". If the text changes to "11/03P YTD", then the resdultt must read March 2111 etc

 

[Andrew Poulsom]

For me the formula returns Feb 2011, because the date is returned as yyyy/mm/dd. I wonder if its a Regional Setting issue?

 

[howard]

Hi Andrew

I live in South Africa and we format our dates dd/mm/yyyy for eg 01/02/2011 being Feb 2011

 

[Andrew Poulsom]

My formulaworks for me with South African regional settings in Control Panel.