You must understand the working of Date#() function. Let me explain in detail.
This function is used to convert the text into Date. So suppose if I have a text 21/01/2012 then I can use the Date#() to convert this text into date format.
This is the name of the Text field which is coming from any of your data source.
This is the format in which the text contains the date.
Text : 21-03-2013
Expression will be : Date#(Text,'DD-MM-YYYY')
Text : 2013/03/19
Expression will be : Date#(Text,'YYYY/MM/DD')
Note : The format should be the same format in which your text is.
i completely know the date# working, you just need to tell me where the expression is wrong.
" When i remove date#([date] , 'YYYY/MM/DD' ) from the alt then it works fine."
This is because the field which is coming from your excel file contains the Data in Date as well as text format.
If you look at the excel file you will find the data
You can see here that the value is in Date format for H but for I it is not.
Yes, i know, but how does the removal of "date#([date] , 'YYYY/MM/DD' )": from alt solves the problem.
this is is just one of the format which was present in the earlier version of this file but not now in this file.
In future sone text in the format YYYY/MM/DD can appear again so i will have to use this which i believe will work then.
But the alt function is not concerned if the format is NOT present in the file, it is concerned IF IT IS present then it converts to the desired default/set format else if none of the mentioned format is present then it will return date(0) i.e. 30/12/1899.
Again, why do we have to remove "date#([date] , 'YYYY/MM/DD' )" from the alt to make it work ?
I am not sure why it is behaving like that, but try by putting that statement at the end i.e before num#() function in your expression and see how it goes.
If you're nt sure why this is happening then i think its quite a problem as i rate you very high,
I can try putting it at the end and it might work but that won't give a logic,why did it work ?
There is something which we're missing or might be a bug.
The 'YYYY/MM/DD' does match your "I" values.
The date parsing rules allow YYYY to be a 2 digit year as well. So "30" is interpreted as "2030".
I think you can resolve the problem by moving the "YYYY/MM/DD" to the end of the list. Then your I values should get picked up by the "DD/MM/YYYY" format which is earlier in the list.