Parsing Dates in Excel Import

Hi all – When importing dates, we have a variety of formats that are being used, so we are trying to maximize for flexibility. When stored as text, 01/01/2023 comes through with that value as expected. However, when stored as a date, 1/1/2023 comes through as 1/1/23 which is parsed as January 1, 0023. Has anyone experienced something similar / have any tricks to allow for either type of value?   We would just use date, but 01/01/2023 stored as text in excel errors out during import when the attribute is a dateTime. 
1 answers

Hi Jon,


I would be carefull with providing this kind of flexibility and rather go to a single input format. Because it will be hard to identify all possible use cases. Have you thought about people using excel in dd/MM/yyyy or MM/dd/yyyy format in this sense as well?

However if that is your case you could do the following:

remove all special characters with a replaceAll() function. Then count the length of the string using the length() function. If it is 6 characters use parsing for ddMMyy, if it is 8 characters you can parse using ddMMyyyy,