How do you upload a duration field from excel?

richard_selby · ‎06-06-2013

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 · ‎02-04-2015

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.

View solution in original post

richard_selby · ‎06-06-2013

Screams in f r u s t r a t i o n ! !

Report Inappropriate Content · ‎06-06-2013

ServiceNow expects durations to have millisecond values.

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

richard_selby · ‎06-07-2013

I tried the code line you mentioned as a transform script, running OnBefore
It worked well. It took my duration in seconds on the spreadsheet, and applied them correctly (I saw the x 1000 line).

However, I still get an error on every line like this:
Unable to format 172,800,000 using format string yyyy-MM-dd hh:mm:ss for field duration

Which makes no difference, I am happy to ignore it. But I cannot see the date format field anywhere on the transform map, so I can't switch it off.

Admin Pro · ‎06-07-2013

If you modify the transform map field maps list view and add date format, then you can modify your date format to your liking.