Find Month Name

Hello Forum,

I am trying to identify month and year from a date in generic format so that :
1. The month value be changed to the name of the month.
2. Identify the last two digit values of the year.
3. Concatenate first 3 characters of month name, a hyphen and last 2 digits of year.

Please go through the attachment. MonthYear.xlsx. Column B is the function output.

Wrote a function for this purpose, but it is not working properly in some cases. Please go through the code:

Please Login or Register  to view this content.

Regards,
Sailaja

Hello OllyXLS,

Tried TEXT() function also. But it also not working for all cases.

Please go through the attachment. Column C displays Text() function output. MonthYear.xlsx

Regards,
Sailaja

Change col A to mm/dd/yyyy instead of dd/mm/yyyy. Excel reads it as mm/dd, note how A10 is 5/11, excel thinks it's May 11.

Hello jwlamb,

I changed the Date format also. I am still getting the same old result given by Text() function.

Regards,
Sailaja

You are right OllyXLS regarding UDF. I uploaded a non macro file.

Coming to Column C : Have you changed the format of the data in the column or it is showing the result as it is. I am not understanding where I am making the mistake.

I am uploading a macro enabled (.xlms) file MonthYear.xlsm
Column D is the changed format of Column A to Date (US Date format mm/dd/yyyy).

Hi Sailaja,

As per your file, Column A contains date in "Date" format, Not in String/Text format. Hence the months <10 in the sheet may appear with "0" Prefix (Ex. 01,02,03...09)

But, in actual it will store as numbers only. So, in your switch case you need to refer them as numbers (without zero prefix).

And convert the month and year to Value through function Val()

m=val(mid(d,4,2))

Your problem can solved in two ways (as said in old posts).

=Text(date(Year(A1),Month(A1),Day(A1)),"Mmm-YY)

the above will doesn't mater about the system date format.

and the second one will be the UDF you written and again you are thinking that date is in DD/MM/YYYY format.

Thanks,
Bhanu

Hi Sailaja,

Please find the attached updated file with corrected code.

Thanks,
Bhanu