Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Ashif
Contributor II
Contributor II

How to get date from "Thu Jan 03 00:00:00 SGT 2019" timestamp format.

Hi All,

My Excel sheet has "Thu Jan 03 00:00:00 SGT 2019" timestamp format and i want to get date (03/1/2019). 

Could you please help on this. Any help would be much appreciated.

 

Thanks,

Ashif 😊

1 Solution

Accepted Solutions
Lisa_P
Employee
Employee

Try this is the load script ..

Load date(Date#(NewDate, 'DD MMM YYYY'), 'DD/M/YYYY') as Date;  // reformat as a date
Load Day& ' ' & Month & ' ' & Year as NewDate;  // Put it back together as 03 Jan 2019
LOAD
   "Date",
   Mid(Date, 5, 3) as Month,   // extract Month (Jan)
   Mid(Date, 9, 2) as Day,  // extract Day (03)
   Right(Date, 4) as Year  // extract Year (2019)
FROM [lib://dAta/Date test.xlsx]
(ooxml, embedded labels, table is Sheet1);

View solution in original post

2 Replies
Lisa_P
Employee
Employee

Try this is the load script ..

Load date(Date#(NewDate, 'DD MMM YYYY'), 'DD/M/YYYY') as Date;  // reformat as a date
Load Day& ' ' & Month & ' ' & Year as NewDate;  // Put it back together as 03 Jan 2019
LOAD
   "Date",
   Mid(Date, 5, 3) as Month,   // extract Month (Jan)
   Mid(Date, 9, 2) as Day,  // extract Day (03)
   Right(Date, 4) as Year  // extract Year (2019)
FROM [lib://dAta/Date test.xlsx]
(ooxml, embedded labels, table is Sheet1);

Ashif
Contributor II
Contributor II
Author

Hi Lisa.

 

Thanks for your great help. Now i am able to get date format as i expected.