Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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...
Another solution might be this
Date(MakeDate(Subfield(DateField, '/', 1), SubField(DateField, '/', 2) * 1), 'DD/MM/YYYY') as DateField
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);
Ok, let me try which option works best, the last one if it works, back soon.
Thanks
This syntax is not working, did you try it in QV or just wrote it on here?
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);
I think that has worked and I've set monthnames back to Jan, Feb.
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')