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: 
madmax88
Creator II
Creator II

Two different date formats in one excel file

Hi,

I have an Excel file with 2 differet date formats:

Example:

Number              Dateformat

5                        01.01.2017

2                        01-JAN-2017

I tried in the script the function

Date(Date#(Dateformat, 'DD-MMM-YYYY'), 'DD.MM.YYYY')

Then it changes the date from number 2 in the correct date (01.01.2017). But the date of number 5 now disappears in the app-->Only the date of number 2 appears in the correct date Format (DD.MM.YYYY), but not the date of number 5. How can i set the function that both Dates are in the app?

1 Solution

Accepted Solutions
marcus_sommer

The NULL result (the - is just a placeholder for NULL) will be caused through the date() which couldn't handle this input.

You could also try the following:

alt(Date(Date#(Dateformat, 'DD-MMM-YYYY'), 'DD.MM.YYYY'), Dateformat)

- Marcus

View solution in original post

7 Replies
marcus_sommer

Try it with:

Date(alt(Date#(Dateformat, 'DD-MMM-YYYY'), Date#(Dateformat, 'DD.MM.YYYY')), 'DD.MM.YYYY')

- Marcus

madmax88
Creator II
Creator II
Author

Doesnt work. Only the Dates of number 2 appearing. The Dates from number 5 are showed as "-"..

In script i did the following:

Date(alt(Date#(Dateformat, 'DD-MMM-YYYY'), Date#(Dateformat, 'DD.MM.YYYY')), 'DD.MM.YYYY') as Dateformat

marcus_sommer

It seems that your value is different in some way - try to check it's real value maybe with: num(), len() or trim(). How is your default date-format defined?

- Marcus

madmax88
Creator II
Creator II
Author

my Default date Format is 'DD.MM.YYYY';

If i dont Change the Format in script i get following in a table

Number         Date

1                   01.01.2017

2                   01-JAN-2017

When i insert ur formula (Date(alt(Date#(Dateformat, 'DD-MMM-YYYY'), Date#(Dateformat, 'DD.MM.YYYY')), 'DD.MM.YYYY') as Dateformat)

i get following

Number      Date

1                  -

2                01.01.2017

marcus_sommer

The NULL result (the - is just a placeholder for NULL) will be caused through the date() which couldn't handle this input.

You could also try the following:

alt(Date(Date#(Dateformat, 'DD-MMM-YYYY'), 'DD.MM.YYYY'), Dateformat)

- Marcus

_armoco_
Partner - Creator II
Partner - Creator II

Hi Max,

I had something like this a few weeks back and my issue was 01.01.2017 being interpreted as MM.DD.YYYY by the excel instead of DD.MM.YYYY even though I made sure that it was formatted in DD.MM.YYYY.

See this thread: Date issues

Try selecting the cell in excel and selecting long date & short date to see how exactly excel is interpreting the date

Regards,

Arjun

madmax88
Creator II
Creator II
Author

Hi,

@Marcus: Your formula works! Thanks

@Arjun: When i set Change the Format in Excel, it doesn't recognize the Format 01-JAN-2017 as date. It takes it as Text.