Calculate a Duration Between the Created Date and Today's Date

Joshua Cassity · ‎12-23-2016

Man oh man.. how frustrating Date/Time calculations can be, am I right?

I built a new table for a customer who's looking to track their build book processes and needs to have four duration fields calculated by the system when he opens a record. I'm thinking if I can get one of them then I can easily get the rest so I've tried a couple of things with no success - see screen below for form layout.

Essentially the customer would like the duration between the system created time stamp (sys_created_on) and the current system date (the problem for me) and to have that duration be shown on the Total Time Open duration field (u_total_time_open).

Has anyone else gone though this kind of nightmare land and can lend a guy some assistance? Did i mention that I really hate date/time calculations.... lol

Bonus Question: I'm pretty sure I can do the other duration calculations since those fields are actually on the form and already being date stamped but will those duration calculations work if the Created field is a (date/time) and the other fields are date only OR do I need to make them date/time as well?

Thanks in advance.

drjohnchun · ‎12-28-2016

Can you try

function onLoad() {

       var strt = g_form.getValue('sys_created_on'); 

       var ajax = new GlideAjax('AjaxDurCalcToday'); 

       ajax.addParam('sysparm_name','durCalcToday'); 

       ajax.addParam('sysparm_strt',strt); 

       ajax.getXMLWait(); 

       var answer = ajax.getAnswer(); 

       g_form.setValue('u_total_time_open', answer); 

}

View solution in original post

Joshua Cassity · ‎12-28-2016

I found that with these calculations it did NOT like only dates because it would default the time to 18:00 hours, which caused the calculations to be a little off. So I simply re-created them to be date/time and everything seems to be working fine now.

Thanks again.

~ J ~

Abhinay Erra · ‎12-23-2016

Here you go.

If one of the field is of date type

var gd= new GlideDateTime();

gd.setValue(gr.<your date field name>);

gr.<duration field name>=gs.dateDiff(gd.getValue(),gs.nowDateTime(),false);

For created field, since it is date/time type you can just use this

gr.<duration field name>=gs.dateDiff(gr.sys_created_on.getDisplayValue(),gs.nowDateTime(),false);

sunnysunny · ‎12-23-2016

gs.dateDiff(gr.sys_created_on.getDisplayValue(),gs.nowDateTime(),false);

so in the above function we can have one field as date and the other as datetime?

Abhinay Erra · ‎12-23-2016

Yes you can use. You can also use GlideDateTime() to convert it to date/time as shown above. Either way you will get the same result.

Joshua Cassity · ‎12-28-2016

You unfortunately can't use gs functions directly in client scripts so this will have to have another approach.

Do I have to put this into a Script Include and then call that script include from the OnLoad script to calculate the field?