Issues with importing date from excel ad formatting to monthyear

[#1]

I am importing a completion date with the following format but i want to group by on mmm-yy

how can i convert this date field - I have tried date(completiondate,'MMM-YY) but it displays nothing - just dash "-"

Is it better to load the dates with the month year or to format them after the load

The purpose is to build a spreadsheet with 12 months of data per month for completion dates

Thanks

Rick

Please see attachment

[petter]

You missed a ' in your expression - or is that just mistyping when you wrote the question?

If not - then make sure that completiondate is actually a real date for QlikView by looking at what Num(completiondate) looks like. That will give you a number in 40000's with possibly some fraction.... then it is a real date to QlikView...

[#3]

Please review the attachment as I have displayed the number format of the date as 35,571.  I still cannot convert to the a month date format?

Thanks

[#4]

when I user num(completiondate) - i get nothing - so its not a number.

now do i use the date# to convert it into a number and then a date format to get the monthyear

Rick

[petter]

If you try to do =Date(35571)   you will get 21st of May 1997 as a date ...

If you do =Timestamp(35571.89) you will get 21st of May 1997 09:21:36 PM

How about the , is it a thousand separator in your language setup?

[#6]

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';

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

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

i believe its a string - so how would i convert it to a date and then a month year?

Thanks

[petter]

Just to be sure I help you with the right problem .... Where do get the dash -   as the result from this expression:

      date(completiondate,'MMM-YY)

Is it in a Text Box, a Chart or where?

If you use the expression without being wrapped in an aggregate function as expression in a chart you will get a

dash if not a single value is picked out with the dimensions in the chart... Could that be the case?

[MarcoWedel]

Hi,

you could try

Date#(completiondate,'MMM-YY')

instead of

Date(completiondate,'MMM-YY')

as

Date# interprets a string as date, while

Date only formats an already numerical value in the date format specified.

hope this helps

regards

Marco

[#9]

Hej der,

I have in my qvw file this settings which are ok

SET DateFormat='DD-MM-YYYY';

But when I import dato from excel i get something like 41306.000000

If i only got the 41360 then qlikview would see it as a date, but it does not because of the .000000

Excel format is date but it sends it with extra ceros.

What can I do to cut the . and the ceros

Thanks in advance

Martha

[#10]

I have by the way solved this issue.

The problem was how I recieved the data from excel