would be a bit easier, if you can attach the datasource to check on the formatting there, what appears to be a date, might be a number (which would be the usual way) - then you can "force" QV to interprete this with a preceding DATE. If it is a text-field, you can interprete it with a formula like DATE#(MyDate, 'DD/MM/YYYY'). If you wish the latter to show as a date, then it should read DATE(DATE#(MyDate, 'DD/MM/YYYY')).
i think i don´t understand what you say or it doesn´t work; now i can see ok the complete date, but i can´t extract year and month. i need it because i´ve got more tables and views from where i take dates but these works ok. I need to extract year and months from this excel to match with the same field on the data base.
i tried extracting them using a expression but it doesn´t work as i need (doesn´t match with the others dates).
Before you determine month or year you should have a date. Understood that you have a problem to read the DATE as a real date. Once you have it you need to reload the table and insert month or year. Thus the script should look like
CrossTable(DATE, Q, 3)
LOAD * FROM
[C:\ budget afiliates 2009.xls]
(biff, embedded labels, header is 1 lines, table is [Budget Arg 2009$])
WHERE(Unidad = 'Q');
Q2: LOAD *, DATE(DATE) AS Date, MONTH(DATE) AS Month, YEAR(DATE) AS Year RESIDENT Q1;
DROP TABLE Q1;
If this does not help, please post some sample from your Excel.
thank you very much Peter.
here i send you a document with the data i´m working and a portion of the excel. i can´t convert DATE in a date format.