Convert date to MMMM YYYY not showing year

[McKnaffens]

I try to show a date in format MMMM YYYY - coming from DD-MM-YYYY. I use the below function to convert the date:

TEXT(B5;"[$-409]mmmm yyyy")

From date '01-12-2016' I would get 'December yyyy'.

Any idea why year is not shown, when month is?

[Glenn Kennedy]

=TEXT(B5;"mmmm yyyy")

or just format B5 to look the way you want. PS Your original also works fine!!!

[Pete_UK]

Is your column wide enough to show the year?

Pete

[McKnaffens]

Year, my column is wide enough - see picture for actual look.

http://fs5.directupload.net/images/170221/jx9iqt8g.png

I understand that this formula should work, but the real question is why it does not for me.

[Glenn Kennedy]

A picture is not much help.

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!


Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[McKnaffens]

After much back and forth I learned that this is an issue with language.

I needed to change my locale on the PC to English (which per default is Danish...) and then it understood the YYYY part.

[FDibbins]

Thanks for the feedback, always good to know little tricks like that

[Pete_UK]

Or you could stick to Danish and use AAAA instead of YYYY.

Hope this helps.

Pete

[xlnitwit]

There are some tips here on working around international issues with the TEXT function- http://www.rondebruin.nl/win/s9/win013.htm
Useful if you need it to work in multiple regions, though it will require saving the workbook in a macro enabled format.