I need to make this date YYYY/MMM into DD/MM/YYYY

[Anonymous]

Hi Guys,

I need to make this date YYYY/MMM into DD/MM/YYYY, this is one hell of an excel spreadsheet, I don't know what it's doing to date, a right nightmare spreadsheet.

If date was YYYY/MMM you would assume its 2016/Jan, but it's doing 2016/001..... why do months need 3 digits?

Regards

[Anonymous]

Yes it works, but setting the Monthnames from Jan to 001 might mess something else up in the application as there's more than one set of dates.

But thanks, it may be my only option.

[sunny_talwar]

You can do like this:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

SET MonthNames='001;002;003;004;005;006;007;008;009;010;011;012';

Table:

LOAD Date(Date#([YEAR QTR], 'YYYY/MMM'), 'DD/MM/YYYY') as [YEAR QTR]

FROM

(ooxml, embedded labels, table is Sheet1);

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

So basically just before you are ready to do a conversion you can SET MonthNames to 001;002. and after it ends, go back to Jan;Feb...

[sunny_talwar]

Another solution might be this

Date(MakeDate(Subfield(DateField, '/', 1), SubField(DateField, '/', 2) * 1), 'DD/MM/YYYY') as DateField

[sunny_talwar]

Sample attached for the second option

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Table:

LOAD Date(MakeDate(SubField([YEAR QTR], '/', 1), SubField([YEAR QTR], '/', 2) * 1), 'DD/MM/YYYY') as [YEAR QTR]

FROM

(ooxml, embedded labels, table is Sheet1);

[Anonymous]

Ok, let me try which option works best, the last one if it works, back soon.

Thanks

[Anonymous]

This syntax is not working, did you try it in QV or just wrote it on here?

[sunny_talwar · Accepted solution]

This one isn't tested, but this one is

Table:

LOAD Date(MakeDate(SubField([YEAR QTR], '/', 1), SubField([YEAR QTR], '/', 2) * 1), 'DD/MM/YYYY') as [YEAR QTR]

FROM

(ooxml, embedded labels, table is Sheet1);

[Anonymous]

I think that has worked and I've set monthnames back to Jan, Feb.

[swuehl]

Another option might be

Date(Date#([YEAR QTR],'YYYY/0MM'),'DD/MM/YYYY') as [YEAR QTR]

tested in a text box using

=Date(Date#('2014/011','YYYY/0MM'),'YYYY-MM-DD')