How do you import date fields from excel?

[#1]

I've been sifting through the Importing articles on the wiki, and trying various settings, but I'm still not having any luck. I want to import a number of tasts and set the Opened date field.

In excel, I've tried using various date formats, the General cell format, and the apostrophe ' prefix in the cell. Whenever I run the import it sets it to a random date like year 0099 AD etc.

[Mark Stanger]

It sounds like there may be an issue with .xls files. You may try using a .csv format and see if that works.

[nikita_mironov]

We also faced the issue while exporting date/time field values from Excel. After several tests t appeared to me that SNC Instance takes the real value from a date/time cell regardless the date format. In other words it takes the date serial value which is the number of seconds since January 1st 1900 if I'm not mistaken. We should give date to service-now in a format that it expects it to get , for example YYYY-MM-DD HH-MM-SS or as we defined in our transform map.

We implemented the following workaround for proper date/time values handling in Excel Export files:
a) Format date/time field column as you want it to be received by SNC
b) Copy the whole column to NOTEPAD (will be stored there as pure text)
c) -= This is key element! =- And new column to your Excel file and change its type to TEXT.
d) Copy dates from your NOTEPAD back to Excel in a new column.
e) Feed the Excel file to SNC Instance.

Only one limitation: the values in that new column field are no longer date/time. These are just TEXT, i.e. you can not perform date/time operations over them in your Excel. This is the main reason why I added brand new column but not replaced the values in origincal column.

CSV files export might be the best solution because it does not require these 4 steps.

Thanks.
N

[quiksilver]

Hi,
if the imported field is just TEXT, how can you transform it to a date field?

[nikita_mironov]

You can specify the date format in transform map. E.g. "yyyy-MM-dd". Date/Time fields will be exported using system time zone.