Date Format

[Anil_Babu_Samineni]

Hello,

I have date field in excel. Now, i want to convert to qlikview date format. Suggest me, Because when i am trying to create one list box with the year it showing 1899 & 2014. But i have only 2014 Data in my excel. Please help me?

My excel date has 3/26/2014  12:00:00 PM

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

2014:

LOAD rep as [2014 rep],

     franchise as [2014 franchise],

     product as [2014 product],

     Date as [2014 Date],

     Year(Date) as [2014 Year],

     Score as [2014 Score],

     groupObservation as [2014 groupObservation],

     name as [2014 name],

     Username as [2014 Username]

FROM

[2014Data.xls]

[shree909]

I think the date coming from the excel is string format, convert it to number

=Year(Floor(TIMESTAMP#('3/26/2014  12:00:00 PM','M/DD/YYYY  hh:mm:ss TT')))  or

=Year(Floor(TIMESTAMP#(Date,'M/DD/YYYY  hh:mm:ss TT')))

Replace the 3/26/2014  12:00:00 PM' with Date.

[Anil_Babu_Samineni · Accepted solution]

Shree,

If we use this, After that i check the list box. List box showing empty.

Year(DATE(FLOOR(Timestamp(Date)),'MM/DD/YYYY HH:MM:SS TT'))

I am using this, I just want to ask you is that is correct one. Because, i got the solution after using this...

[shree909]

ur missing the # sign after Timestamp and  use the lower case for the time format as hh:mm:ss

Year(DATE(FLOOR(Timestamp#(Date)),'MM/DD/YYYY hh:mm:ss TT'))

[Anil_Babu_Samineni]

If i take the #. In list box it showing null.

Without # It's working fine.

[shree909]

can you please share sample data..

if possible  add this year to the script;

[vinieme12]

OR you can use

=Year(SUBFIELD(Date,'  ',1))

[sunny_talwar]

If this is working:

Year(DATE(FLOOR(Timestamp(Date)),'MM/DD/YYYY HH:MM:SS TT'))

Then try this:

Year(Date)

All those extra functions should not be required to calculated a Year field.

[Anil_Babu_Samineni]

Yes sunny, that is working.

You mentioned two expressions

Year(DATE(FLOOR(Timestamp(Date)),'MM/DD/YYYY HH:MM:SS TT'))

OR

Year(Date)

Note: As i understand, after see this those two are same right?

When we use the Timestamp and Timestamp# . What are the major diff. Give me simple example for me if you are free...

[Chanty4u]

Hi anil,

timestamp(expression [ , format-code ])

The timestamp function formats the expression as a date and time according to the string given as format-code. If the format code is omitted, the date and time formats set in the operating system are used.

Examples:

The examples below assume the two following operating system settings:

	Default setting 1	Default setting 2
Date format	YY-MM-DD	M/D/YY
Time format	hh:mm:ss	hh:mm:ss

timestamp( A ) where A=35648.375 returns:

	Setting 1	Setting 2
String	97-08-06 09:00:00	8/6/97 09:00:00
Number	35648.375	35648.375

timestamp( A,'YYYY-MM-DD hh.mm') where A=35648 returns:

	Setting 1	Setting 2
String	1997-08-06 00.00	1997-08-06 00.00
Number	35648	35648

timestamp#(expression [ , format-code ])

The timestamp# function evaluates the expression as a date and time according to the string given as format-code. If the format code is omitted, the default date and time formats set in the operating system are used.

Examples:

The examples below assume the two following operating system settings:

	Default setting 1	Default setting 2
Date format	YY-MM-DD	M/D/YY
Time format	hh:mm:ss	hh:mm:ss

timestamp#( A ) where A=8/6/97 09:00:00 returns:

	Setting 1	Setting 2
String	8/6/97 09:00:00	8/6/97 09:00:00
Number	-	35648.375

timestamp#( A, 'YYYY-MM-DD hh_mm' ) where A=8/6/97 09_00 returns:

	Setting 1	Setting 2
String	1997-08-06 09_00	1997-08-06 09_00
Number	35648.375	35648.375