Description
Use a calculated variable to display a categorical amount of time between two dates of interest.
How To
Table:
The final table could look like the below image. In this example, the column of time categories designate the number of weeks before a phone call is due.
Pick List:
A pick list is required to categorize the number of weeks or whatever time value is being used:
Calculating the amount of days:
The JavaScript function to calculate the time between the two dates :
function diffInDaysUTC (a, b)
{
var milliSecondsPerDay = 1000 * 3600 * 24;
var baselineDate = Date.UTC(a.getFullYear(), a.getMonth(), a.getDate());
var followUpDate = Date.UTC(a.getFullYear(), a.getMonth(), a.getDate());
return Math.floor((baselineDate - followUpDate) / milliSecondsPerDay);
}
The diffInDaysUTC method calculates the number of milliseconds between the two dates (baseline and 30 day follow up date, in this case) and accounts for any time differences or time changes that may occur.
In order to convert milliseconds to days, a division of the number of milliseconds by the number of milliseconds per day is required.
Categorizing the amount of days into a pick list value:
The following code will calculate which amount of days should correspond with which particular pick list value:
var dateDifference = diffInDaysUTC(V3, V4);
if(dateDifference < 0) { return 0;}
else if (dateDifference <= 7 && dateDifference > 0) { return 1;}
else if (dateDifference > 7 && dateDifference <= 14) { return 2;}
else if (dateDifference > 14) { return 3;}
End result:
The final code in this example:
