I have always had success formatting the column as date in Excel (so that it displays 12/28/14 but in reality is an integer representing the serial number of days since january 1, 1900. Note: I learned, after a bit of head scratching, that Mac 2008 and earlier use the number of days since January 1, 1904, so if you tried to import a spreadsheet that originated on an older Mac, you'll see your dates about 4 years different than what you think they should be.
The way i do it: - first define an exta column in the excel spreadsheet to convert the date to a string column: Formula (in dutch version) : =TEKST(CW2;"d-m-jjjj") - define an action in the Excel importer to call a microflow which converts the string column to a date field. define a date field en use: parseDateTime($String, 'd-M-yyyy')
In my case i don't want the time-component and i defined the date-field as NON-localized my conversion was extended to: addDays(trimToDaysUTC(parseDateTime($String, 'd-M-yyyy')),1)
Perhaps it's not the best way but it works for me.
if you convert '01-01-2015' using parseDateTime($String, 'd-M-yyyy'); this results in '31-12-2014:23:00' if you convert '01-01-2015' using addDays(trimToDaysUTC(parseDateTime($String, 'd-M-yyyy')),1); this results in '01-01-2015:00:00'
If you trying to import just an attribute that is of type datetime then ensure that the date is formatted ‘mm/dd/yyyy’.
The date should look like 1/12/2020 and not 01/12/2020.
This worked in my cases, and I found that if the date was structured 01/12/2020, even though it was a date, it could not be parsed.