Just a cool little formula that can compare 2 dates/times and provide a duration of how long it took from start to finish.
This can be used in a TON of use cases.
In this case, we’re looking at a “response time” of when a user creates a “support ticket” and when a different user replies to the “support ticket”.
Here’s the js:
var total = InsertCustomFormulaHere;
var days = Math.trunc( total );
var b = total - days;
var c = b * 24;
var hours = Math.trunc( c );
var d = total * 24 * 60;
var e = days * 24 * 60;
var f = hours * 60;
var g = d - e - f;
var mins = Math.trunc( g );
if (days > 0){return days + ' d ' + hours + ' h ' + mins + ' m';} else if (days == 0 && hours > 0){return hours + ' h ' + mins + ' m';} else {return mins + ' m';}
So how does this work?
var total = Custom Formula;
We take the ticket reply created date (this would be the “END” time) and subtract the ticket created date (the “start” time). This is the total time it takes from Ticket creation to Reply.
var days = Math.trunc( total );
The initial value will display the number of days. The numbers following the decimal point are the hours and minutes. So for the days, we use a javascript integer math function called truncate to the total. So if it was 4.23423523, it would return the value 4.
var b = total - days;
var c = b * 24;
var hours = Math.trunc( c );
To calculate the number of hours, we first have to subtract the total time and the days variable. This will give us the integer number that we removed with the truncate function. We then multiply that value by 24, which will then provide us with the number of hours that we also need to truncate (to remove the minutes).
var d = total * 24 * 60;
var e = days * 24 * 60;
var f = hours * 60;
var g = d - e - f;
var mins = Math.trunc( g );
Then, we calculate the minutes. We take the total multiplied by 24 (converts to hours) then by 60 (converts to minutes). We do the same for the days variable. For the hours variable, we only multiply that by 60 to convert it to minutes since it’s already in hours. We then subtract the total by the days and hours. The remaining value is the number of minutes and the numbers following the decimal would be the seconds. So we truncate that to complete the mins variable.
if (days > 0){return days + ' d ' + hours + ' h ' + mins + ' m';} else if (days == 0 && hours > 0){return hours + ' h ' + mins + ' m';} else {return mins + ' m';}
The last function is an If/else statement. The formula checks days and hours and it returns the proper message.
Demo:
The ticket creation date is set to 7/3/2022 at 3:03 pm.
Result:
