Date type field value in Excel import is incorrectly set on the target table for some users with an invalid date - Support and Troubleshooting

Date type field value in Excel import is incorrectly set on the target table for some users with an invalid date Issue  Symptoms of this issue is the User's profile Date Format impacting the Excel import of Date type field, creating invalid dates on the target table.

Release 1. Staging table with the 'String' type field (eg: WarrantyExpiration field in the screenshot below) for the data coming from date to get transformed on the Target table.

2. Transform map field mapping as below:

3. Set the Date format of the user who is running the import something different from "YYYY-MM-DD" format (eg: dd-MM-yyyy) as shown in the screenshot below:

Cause The Date format in the Staging table is "dd-MM-yyyy", based on User's profile date format, but the Date Format set on the Transform map field mapping for the source field is "yyyy-MM-dd", therefore causing a mismatch issue.

NOTE : Out of the Box, ServiceNow instances look at the User's profile date format when the date format is set on the spreadsheet which is getting imported, and import the date value in the User's profile date format in the staging table.

Resolution There can be the following alternative solutions to this issue:

Empty out the Date Format from the Transform map field mapping for the filed which is bringing the Date value.

Remove the date format from the Date column of the Excel itself.

Convert the Excel file to CSV and then run the import. This will correctly set the date in the staging table as there is no date format on CSV.