topic Re: Change date format!! in QlikView

Change date format!!

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

hi all..I have a scenario where i read data from the excel spreadsheet which has a date column of format(MMM YYYY), but it is interpreting the data present in that column as an integer..my dates are like [42644.0] for oct 2016, [42614.0] for sept 2016 and so on..I tried everything but nothing worked for me..I guess QV is taking it as text.. In This case how do i proceed as to get the desired output in the MMM YYYY format..please help!!

Re: Change date format!!

vinieme12 — Tue, 31 Jan 2017 06:17:40 GMT

Date#(fieldname,'MMM YYYY') as fieldname

Date(fieldname,'MMM YYYY') as fieldname

Use the above during load

Re: Change date format!!

jonathandienst — Tue, 31 Jan 2017 06:25:01 GMT

The underlying value in Excel is the date number, so all you need in QV is to format it as a date:

Date(FieldName) as Date

Re: Change date format!!

Anonymous — Tue, 31 Jan 2017 08:22:05 GMT

guys thanks for help..but didn't work for me..I've tried like almost everything..can't fix it..QV is reading it as text..num(), num#(), alt(), date() or date#() none are working..is there any other way out to fix this?

Re: Change date format!!

vinieme12 — Tue, 31 Jan 2017 08:34:59 GMT

go to Number Tab >> Select Date >> in format expression type MMM YYYY

or post the app the demonstrates the problem

Re: Change date format!!

buzzy996 — Tue, 31 Jan 2017 15:29:40 GMT

sample app,pls?

Re: Change date format!!

buzzy996 — Tue, 31 Jan 2017 15:34:46 GMT

or try this way :

Date(AddYears(Date(Date#('19'&Right(yrdatefield,6),'YYYYMMDD'),'DD/MM/YYYY')

,100*Left(yrdatefield,1)),'DD/MM/YYYY') As NewDatefield;

Re: Change date format!!

sangeess21 — Tue, 31 Jan 2017 15:37:53 GMT

Goto Settings--> Document Properties-->Number tab--> Select the date field and change the number format settings to Date. (Change the format pattern to MMM/YYYY)

Thanks,

Sangeetha