date#([date], 'MM-DD-YY' ), num#([date], 'M-DD-YY' ),0)) as date
FROM
test.xlsx
(ooxml, embedded labels, table is Sheet1);
Please note that although the format 'YYYY/MM/DD' is not present in the excel sheet but still i'm using it as this format used to be there in an earleir version of the file & could be there in future also.
The problem is the conversion is correct for every name exect for name: I for which I get :
13/02/2028
13/04/2030
12/06/2030
13/01/2031
13/03/2031
My set/default date format in the file is DD/MM/YYYY.
When i remove date#([date] , 'YYYY/MM/DD' ) from the alt then it works fine.
Given your two formats in your example, 12/06/30 is a valid value in both date formats. So there is an ambiguity if you want to get the 'true' date.
It's always a good thing if your input data is in a well defined format, so if you can get your users to use one format, that would be much better than guessing around when reading in, right?
If possible, use a four digit year format. QV will use some AI to determine the full four digit year (e.g. determine 2013 from 13). I believe it will interprete 63 - 99 as being in 20th century, and 0 to 62 in 21st century (range is +-50 years from today).
Maybe this is according to what your users expect, maybe not.
And finally, there is an ISO standard for dates, which avoids the DD/MM/YY(YY) resp. MM/DD/YY(YY) etc. ambiguity altogether: 'YYYY-MM-DD'
