Two types of Date formats in Excel (general,date) How to load?

[hrithikroshan123]

Hello All, i got stuck in loading the data from excel. kindly help me on this.

As it contains two types of data format one is Date and other is general, i would like to convert this Datastarted field data into DD/MM/YYYY format. 

Attached is the excel

[lironbaram]

hi 

this should work for you 

if(isnum([Date Started]), date( [Date Started]),date(Date#([Date Started],'MM/DD/YYYY'))) as newDate,

[hrithikroshan123]

Thank you for your reply. But it is not working. The file contains data for July and August month only.

But it is showing for other months also.

i am attaching the data file as well, which i used here

kindly suggest

[MindaugasBacius]

I used your data set and did some transformation:

Directory;
LOAD [Date Started], 
	Date(If(Index([Date Started], '/') <> 0, Date(Date#([Date Started], 'MM/DD/YYYY'), 'YYYY.MM.DD'), [Date Started]), 'DD/MM/YYYY') as new,
     Status
FROM
[..\Downloads\data1.xlsx]
(ooxml, embedded labels, table is data);

This is what I got:

[marcus_sommer]

It seems that your data (the numerical value of the numbers) are different to what you think they are. The suggested transformations are working. You may try to bypass it with something like:

alt(date(date#([Date Started], 'MM-DD-YYYY')),date(Date#([Date Started],'MM/DD/YYYY'))) as newDate

but I suggest that you check the numbers again and maybe also the process they took into this excel.

- Marcus

[hrithikroshan123]

Thank you for the reply. i tried it and i could see the same result as yours in front end. 

but when i apply month on the field new, it shows more months which i am not expecting. however it should show only july and august

LOAD *, MONTH(new) AS [MONTH of NEW field];

LOAD *,
Date(If(Index([Date Started], '/') <> 0, Date(Date#([Date Started], 'MM/DD/YYYY'), 'YYYY.MM.DD'), [Date Started]), 'DD/MM/YYYY') as new;

LOAD [Date Started],
Status
FROM
[D:\data1.xlsx]
(ooxml, embedded labels, table is data);

[hrithikroshan123]

Thank you Marcus, for the reply.

i tried your code, but it is giving month for July only and August is missing.

kindly suggest. To iterate again the excel contains two formats (general and date) for Date Started field.

And in this format only i am getting the data.

Can we do something else in qlikview to get july and august month, instead of going back to the user for updating the format of Date Started field. Please suggest. Or it is the only way to update the excel file with consistent format for Date started field. kindly suggest.

LOAD *, MONTH(newDate) AS [MONTH of newDate];

LOAD *,
alt(date(date#([Date Started], 'MM-DD-YYYY')),date(Date#([Date Started],'MM/DD/YYYY'))) as newDate;

LOAD [Date Started],
Status
FROM
[D:\data1.xlsx]
(ooxml, embedded labels, table is data);

[marcus_sommer · Accepted solution]

You may try to reverse days and month from your real date-entries like:

alt(makedate(year([Date Started]), day([Date Started]), month([Date Started])),
      date(Date#([Date Started],'MM/DD/YYYY'))) as newDate

but it's quite risky because the next time the dates might come in a different (correct) way again and it may be quite difficult to impossible to differ between correct and uncorrect entries. Therefore I suggest that you talk with your users to ensure that there are always only valid data - it could be enforced in excel with the feature of "valididty".

- Marcus

[hrithikroshan123]

Thank you so much Marcus. It worked and i will take care of the risk you highlighted.