Two different date formats in one excel file

[madmax88]

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?

[marcus_sommer]

Try it with:

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

- Marcus

[madmax88]

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]

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 · Accepted solution]

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_]

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]

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.