5 Replies Latest reply on Mar 1, 2017 11:18 PM by Norbert Maijoor

MONTH CONVERTING :       MTH Oct 2016"  into DATE format

How can I convert "       MTH Oct 2016"  into DATE format?

my files month format is like    "      MTH Oct 2016", "      MTH Nov 2016" and "      MTH Dec 2016"

• 1. Re: MONTH CONVERTING :       MTH Oct 2016"  into DATE format

Hello,

You need to create calculated field in a date format like 15/Oct/2016.

So in your case you can write formula like

"15/" + mid([FieldName],5,3) + "/" + Right([FieldName],4)

this will return 15/Oct/2016. Then convert it to Date datatype.

Hope this help.

2 of 2 people found this helpful
• 2. Re: MONTH CONVERTING :       MTH Oct 2016"  into DATE format

Hi Seungjou,

Find my approach as reference below and stored in attached workbook version 9.3 located in the original thread

Step1: Define Month notation: mid([Month],5,3)

Step2: Define Year notation: mid([Month],9,4)

Step3: Define Month converter

if [Month notation]="Jan" then "1"

elseif [Month notation]="Feb" then "2"

elseif [Month notation]="Mar" then "3"

elseif [Month notation]="Apr" then "4"

elseif [Month notation]="May" then "5"

elseif [Month notation]="Jun" then "6"

elseif [Month notation]="Jul" then "6"

elseif [Month notation]="Aug" then "8"

elseif [Month notation]="Sep" then "9"

elseif [Month notation]="Oct" then "10"

elseif [Month notation]="Nov" then "11"

elseif [Month notation]="Dec" then "12"

END

Step4: Define Date: date("1-"+str([Month Converter])+"-"+str([Year Notation]))

Step5:  Drag Date to rows as first object right click > textmenu  > Month

1 of 1 people found this helpful
• 3. Re: MONTH CONVERTING :       MTH Oct 2016"  into DATE format

Thank you very much.

Due to company policy, I can use 10.1 version now.

If I can use 10.2, I will use your recommendation,

Thank you again.

• 4. Re: MONTH CONVERTING :       MTH Oct 2016"  into DATE format

Dear Maijoor

Thank you very much.

I solved my problem with your suggestion.

for your imfomation, I changed 2 parts.

==========================================

elseif [Month notation]="Jul" then "6"   => elseif [Month notation]="Jul" then "7"

Step4: Define Date: date("1-"+str([Month Converter])+"-"+str([Year Notation])) => date(str([Month Converter])+"1-"+"-"+str([Year Notation]))

Thank you. Have a good day

• 5. Re: MONTH CONVERTING :       MTH Oct 2016"  into DATE format

Hi Seungjou,

Thanks for the remarks. I should have "validated" my post before uploading.