How to map a date or date/time value correctly in a transform map?SummaryWhen loading data from an Excel file into an import set table, fields could possibly be created in date or date/time data type automatically in the import set table depends on the data formatting being used in user's Excel file. During transform the field values into the target table via a transform map, users may observe unexpected behavior, in particular when coalesce is set as true on the field mapping for date or date/time fields. Some common issues can be observed as below: Issue 1When coalesce=true is set on a date type field mapping, after transform the date value is changed to an unexpected date value in the target table. For example, date value is '30-11-2017' in source table and it changes to '09-05-0036' in the target table. Issue 2When coalesce=true is set on a date/time type field mapping, after transform the date/time value is changed to a value in UTC timezone, ignoring user's timezone settings. For example, user is in Japan timezone and let us say date/time value is '2021-02-12 11:29:00' in the source table. After transform it changes to '2021-02-12 02:29:00' (-9 hours which is in UTC timezone) in the target table. Issue 3When date/time type field is being selected in field mapping, even user selects the correct date format and can still see below error message occurs during transform. The date/time value can not be transformed and the field leaves as empty in the target table. "Unable to format 2021/02/12 11:30:00 using format string yyyy-MM-dd HH:mm:ss for field <source_field_name>"ReleaseApply to all releasesInstructionsServiceNow platform only supports String field type in the import set table. Users don't need to manually change the field type in the import set table. Sometimes due to specific data formatting in the Excel file which results in creation of fields in date or date/time field type in the import set table. This is not supported usage and user needs to correct their Excel data formatting before data import. Below are some common solutions to resolve above issues. Solution 1 If user observed fields are created in date or date/time field type in the import set table, change data formatting in the Excel file first. This will make sure all the fields will be created in String field type in import set table.For example, copy the original cell values out and change the cell formatting to 'Text' and copy back the values into the cell again. Note 1: Once the field is created in date/time type in the import set table, the field type can not even be changed to String in table dictionary as there is no String option. User needs to drop the import set table, change the data formatting in Excel and import it again. Please refer to below screenshot. Note 2: If the field is created in date type in the import set table, the field type can be manually changed to String type. However, user still needs to change the data format in the Excel file to avoid the same issue happens again. Note 3: Always make sure correct date format is being selected in the field mapping. The format should be consistent with the imported date or date/time values in the import set table. Solution 2 For date/time field, even if the field is created in String type in import set table and user still get above error in issue 3 during transform. This is because date/time field value needs extra script to convert the value into the target table. Please follow below steps and screenshots. Step 1: In the field mapping, click to open the field mapping for the date/time field Step 2: Tick the [Use source script] box and in the source script, add below two script lines to get the date/time value programmatically.==============================================var gdt = new GlideDateTime(source.<source_field_name>);return gdt.getValue();============================================== Step 3: Click on update to save the change. Now the source field has been changed to [Script] in the field mapping, please refer to below screenshot. Go ahead to import and transform the data, now users can see the date/time value can be transformed into the target table successfully.