Advanced Calculations- Time, Date, Decimals

  • Updated

Calculating the Time Between Time Fields

You can use a Computed Label field to calculate the time difference between two time picker fields. In the example below, we have a time field with the key "time1" and another with the key "time2". 

In the example below, we calculate the total hours between the two date values.  You can adjust the formula to convert to minutes, seconds, or days according to your needs. 

NOTE:  You will need to replace the $time1$ and $time2$ fields with your own Date Selector field keys or the script will not work.

 

ADD THIS TO YOUR CALCULATION FIELD

var date1 = dateFromString($time1$);
var date2 = dateFromString($time2$);
var hours = Math.abs(date2 - date1) / 36e5;

return hours;

function dateFromString(isoDateString) {
  var parts = isoDateString.match(/\d+/g);
  var isoTime = Date.UTC(parts[0], parts[1] - 1, parts[2], parts[3], parts[4], 0);
  var isoDate = new Date(isoTime);

return isoDate;
}

 

Calculating the Difference in Years Between Two Date Fields

To calculate the difference in years between two dates you will need to use a computed label field.   A sample script is provided below:

NOTE:  You will need to replace the $date1$ and $date2$ fields with your own Date Selector field keys or the script will not work.

 

ADD SCRIPT TO A COMPUTED LABEL FIELD


var date1 = dateFromString($date1$);
var date2 = dateFromString($date2$);
var dateDiff = new Date(date2 - date1);

var yearDiff = Math.abs(dateDiff.getUTCFullYear() - 1970);

return yearDiff;

function dateFromString(isoDateString) {
 var parts = isoDateString.match(/\d+/g);
 var isoTime = Date.UTC(parts[0], parts[1] - 1, parts[2], parts[3], parts[4], parts[5]);
 var isoDate = new Date(isoTime);

return isoDate;
}

 

Forcing Decimals on Calculation Fields 

By default, calculation fields will shorten any decimals with "0" on the end of a number.  

 

If your value is: "1.20"

It will shorten it to: 1.2

 

Sometimes, however, you will want to force it to display the "0" at the end. To do this, we are going to have to use the "toFixed()" method.

 

• Note: For Android devices, you are required to put a character in front of the result for it to be left as a string and keep the decimals (see example 2)

 

• Example 1 

var example = decimal($numeric_1$,0);

example = example.toFixed(2);

return example;

 

• Example 2 

var example = decimal($numeric_1$,0);

example = example.toFixed(2);

return "$ " + example ;

 

 

 

 

 

Related articles