Glide Duration - Value Calculation?

[gaidem]

I have found that the database is storing a glide_duration in an interesting format:

Display:2 Days, 4 Hrs, 34 Mins, 45 Secs
Value:25571.1908

Display:2 Days
Value:25571

Display:1 Day, 5 hrs
Value:25570

Display:0 Days, 1 hr, 30 Mins
Value:25569.0625

Display:2 Days, 21 Hrs
Value:25571.875

Does anyone know what the conversion here is??

[Valor1]

Those look like excel values, not Service-Now values.
http://www.cpearson.com/excel/datetime.htm

Service-Now stores all dates, times, and durations as milliseconds from Jan 1, 1970. For dates and times, an offset is applied.

To see this value, use these two methods:

var curr = current.u_duration.dateNumericValue();
gs.log("Duration is: " + curr);
var totalMs = curr + 24*60*60*100; // add a day
current.u_duration.setDateNumericValue(totalMs);

[Arlen]

valor

Hi Valor,

Just a quick note on this; to calculate the 'totalMs' from your line above I think you're missing a '0' from the end. I think it should read:

var totalMs = curr + 24*60*60*1000; // add a day

Now, somewhat related to this topic is the import of duration values particularly when dealing with SLAs.

In my experience I found that unless you can perform the whole conversion in an 'OnBefore' transform script, doing the duration conversion in excel from milliseconds to 'ddd hh:mm:ss' then importing it into the sla_contract table is quicker.

For everyone's benefit here's what I used in excel for the conversion from milliseconds. Please note I did not bother with seconds as most people only round to minutes 🙂

=ROUNDDOWN(C2/86400,0)&" days "&ROUNDDOWN((C2-((ROUNDDOWN(C2/86400,0)*86400)))/3600,0)&" hrs "&(C2-((ROUNDDOWN(C2/86400,0)*86400)))-(ROUNDDOWN((C2-((ROUNDDOWN(C2/86400,0)*86400)))/3600,0)*3600)&" minutes"

The above will display the milliseconds duration in 'ddd hh:mm:ss' format which as per wiki article here:
http://wiki.service-now.com/index.php?title=Setting_the_Duration_Field_Value
still needs to have one more transformation just before import.

That is achieved using an 'OnBefore' transform script like below:

var dateTime = source.u_duration;
target.duration.setDisplayValue(dateTime);

I'd be interested if anyone has come up with an 'OnBefore' transform script for the complete conversion at the time of import.

Cheers,
-Arlen-

[tbalestreri3]

Hi Arlen, I remember meeting you at K11, hope all is well.

Are you importing milliseconds in the duration column in excel?

I have been trying to import tasks via excel and cannot get the duration to stick and calculate the end date, it always defaults to 1.

[Arlen]

tbalestreri3

Hello there, sorry I didn't catch your name from the forum post but thank you I'm well 🙂

Typically a column in excel will contain the value in milliseconds, however, the column cannot be imported to ServiceNow without manipulating it to display in the following format: 'ddd hh:mm:ss'

That's what the excel formula does. It converts the milliseconds to ServiceNow Glide DateTime format.

Hope this clarifies it.