Convert Mon'YY to MM/DD/YY.

[Anonymous]

Hi All,

Convert Mon'YY  to MM/DD/YY.

Ex: April'15 to 04/01/15.

Thanks,

Krishna

Month
April'15
Aug'15
Dec'15
Feb'16
Jan'16
July'15
June'15
May'15
Nov'15
Oct'15
Sept'15

[Chanty4u]

try

=Date(Date#(Month,'MMM''YY'), MM/DD/YY) as NewMonth

[awhitfield]

Have you posted the wrong xlsx, there are no dates in it!

Andy

[sunny_talwar]

I don't see Month field in your attached Excel

[Anonymous]

Hi Sunny,

I'm Sorry. Just now I attached 1.Xlx. Please delete  attached file.

Thanks,

Krishna

[Kushal_Chawda]

try this

date(monthstart(date#(purgechar(Month,chr(39)),'MMMYY')),'MM/DD/YY') as Month

[tresesco]

Alt(Date(Date#(String2, 'MMMM''YY'), 'MM/DD/YY'),

          Date(Date#(String2, 'MMM''YY'), 'MM/DD/YY')) as Date

Note: Single quote twice (in red)

Replace 'String2' with your field name.

[senpradip007]

Try like

LOAD *,

  MakeDate(Year(New), Month(New), 1) as New1;

LOAD *,

date#(replace(Month, Chr(39), ''), 'MMMYY')  as New Inline [

Month

Apr'15

Aug'15

Dec'15

Feb'16

Jan'16

Jul'15

Jun'15

May'15

Nov'15

Oct'15

Sep'15

];

[sunny_talwar]

Here is another option:

SET MonthNames='Jan;Feb;Mar;April;May;June;July;Aug;Sept;Oct;Nov;Dec';

Table:

LOAD Date(Date#(Month, 'MMM''YY'), 'MM/DD/YYYY') as MonthYear,

  Month

FROM

[1.xls]

(biff, embedded labels, table is Sheet1$);

[Chanty4u]

Did u try my expression?