I am importing an excel file that has a date field with the following format: dd/mm/YYYY
It imports the data OK, however it does something strange with the dates. If the day is higher than 12, then it takes the current date.
I found this on the log:
WARNING: unrecognized date format “, assigning current date
For example: the date 08/04/2020 is imported ok because 08 <= 12, however the date 23/02/2020 is not imported because 23 is not <= 12, and then it takes the current date.
Any ideas about what is happening?
2
Answers
This issue can get confusing with Excel, since typically, if the system is set for U.S. locales, Excel converts standard European date format in day-month-year to some version of month/day/year. In other words, tThe forward slash usually indicates American format, which is month first, as in April 9, 2022 or 04/09/22. You can force day/month/year, and apparently that was done for your file or system, but that’s not an expected or standard format.
Without examining All Import code in detail, I’d say the behavior you describe implies it is presuming the standard usage. I’d therefore recommend that you try converting your date column before attempting imports, but how to do that efficiently would probably be more an Excel question than a WordPress question, unless you are able in your version of All Import to perform a custom conversion on the field.
A trick that sometimes works on this type of problem is copy-pasting Excel data into a Google Sheet, then downloading as CSV, without ever opening the file in your system before re-uploading. Google Sheets is pretty good about such conversions in general, but no guarantees: Your source file might defeat it in this case.
I had the same issue. Change the dates in your csv file to mm/dd/yyyy and when it imports it will automatically convert to dd/mm/yyyy if that is what your WP website is set up to use.
Only numbers lower than 12 will work the way you are importing because there are only 12 months in a year and it is reading you dd as mm.