Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 😊
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);
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);
Hi Lisa.
Thanks for your great help. Now i am able to get date format as i expected.