Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have been desperately trying to load a date field from XLS. Its format goes like:
From Date |
January 12, 2015 |
January 19, 2015 |
January 26, 2015 |
February 2, 2015 |
February 9, 2015 |
February 16, 2015 |
February 23, 2015 |
I need to get it in this format in QV:
05/01/2015
09/02/2015
23/03/2015
...
None of my attempts was successful (null values are returned 😞
Num(Date#([From Date], 'MMM DD, YYYY')) AS NumericDate,
Date(Num#([From Date]),'DD/MM/YYYY') as attempt,
Date(Num#([From Date]), 'DD/MM/YYYY') as date3,
Week(Num#([From Date])) & '' as weekTest,
Date(Date#(Num([From Date]), 'MMM DD, YYYY'),'DD/MM/YYYY') as date4,
Date#(Num#([From Date]), 'MMM DD, YYYY') as date6,
Date(Date#(Num#([From Date]), 'MMM DD, YYYY'), 'DD/MM/YYYY') as date8,
Date(Date#('September 24, 2007', 'MMM DD, YYYY'),'DD/MM/YYYY') as date5 // works perfectly
Can you please help? Dates have always been my nightmare
Many thanks.
If the dates are really dates in Excel, not text; you can just load the field. QV should recognize the date and format it according to the Set DateFormat command.
try
date(date#([From Date],'MMMM DD YYYY'),'DD/MM/YYYY') as From_Date
if possible share the excel file with sample data
Try what Kush141087 has provided. Since you date include the full month name, MMM most likely won't work, but MMMM should work.
So Date#([From Date],'MMMM DD YYYY') is telling it to understand January 12, 2015 and Date(,'DD/MM/YYYY')) to convert the date to your required format.
Thank you folks. The above solution did not work though. Tried as:
WEEKS:
LOAD
[From Date] as Original,
Date(Date#([From Date],'MMMM DD, YYYY'),'DD/MM/YYYY') as fullNameMonhtsWithComma,
Date(Date#([From Date],'MMMM DD YYYY'),'DD/MM/YYYY') as fullNameMonhtsWoComma
FROM
$(vDataFile)
(ooxml, embedded labels, table is Weeks);
Result:
Have not checked the solution provided by Mike Woolf yet but it looks promising.
Mhm, weirdly enough not even this solution worked:
Converting text to date format while importing from web
I wonder whether sth could be broken in XLS metadata? Hence sharing the source file - PFA.