How do you upload a duration field from excel?

[richard_selby]

Does anyone know how to upload SLA durations from a spreadsheet?
When I export from SN, the system converst to seconds, e.g. 86400 for 1 day.
But, boy oh boy, does it not like it that way on import.

When I upload new SLAs from a spreadsheet (xls format). I get an error for every row, e.g.
Unable to format 86,400 using format string yyyy-MM-dd hh:mm:ss for field duration.

In an SLA record, display format for duration is dd hh:mm:ss
I've tried typing in dd hh:mm:ss format on spreadsheet but get the same error again.

[richard_selby · Accepted solution]

Funnily enough, I was wondering about the same issue 20 months later. I looked it up here on the Community page and was surprised to see my original question. Clearly I am older but not wiser.

Despite the fact it likes to grumble about format strings, ServiceNow doesn't show the Date Format field on the Field Map   form page. A UI Policy hides it form view, even though it probably shouldn't. So following Matt's suggestion, I added the Date Format field to the list view on the Field Maps related list. I tried a few formats but still got the error date format error. So no joy there.

Next, I deleted the duration->duration field map entirely.Then in the Transform Map proper, I ticked the Run Script box, and entered the single line of code offered by Laurens above:

target.duration.setDateNumericValue(source.u_duration*1000);

This sets the duration appropriately. I have my duration defined in seconds on the spreadsheet, and upload untroubled by any nasty errors.

[chandanveeranna]

Hi Richard,

I have a custom table which consists of duration type field. I am trying to import data from excel which consists of duration represented in seconds but it is not entering the even after using the above single line code.

However only the ones that is mentioned as 900 (ie 15 mins) is getting populated and rest all seems to be empty. Kindly let me know if you can help.