Date Formula Fields
We provided several formulas below that you can copy and paste into the JavaScript field of the Formula Fields window. The following example shows the Work Day Difference formula for determining the number of working days since the dates in the Created Date field:
Result:
The following are some examples of different formulas that you can use. These examples assume a typical work day of Monday through Friday, 9:00 a.m. to 5:00 p.m.
- Change the work days as needed using the workDays array.
- Change the working hours as needed using the startHour and endHour variables.
- Refer to the kendo number formatting and date formatting documentation, for details on the kendo.format specification.
Work minutes difference
This formula returns the number of work minutes between two specified dates.
(function(){
var startHour = 9;
var endHour = 17;
var workDays = ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday"];
var minutes = 0;
var dateField = new Date(Date1__c); // This is the first date field in Salesforce
var todaysDate = new Date(Date2__c); // This is the second date field in Salesforce
// To specify today instead of Date2, use new Date(). For example:
// var todaysDate = new Date();
while(true) {
//compare Date1 to today
if(dateField.compareTo(todaysDate) < 0) {
// (…if…) ? (…then…) : (…else…)
// If the date is within the workDays array and within work hours then add 1, else 0
minutes += (workDays.indexOf(dateField.getDayName()) > -1
&& dateField.getHours() >= startHour
&& dateField.getHours() < endHour
? 1 : 0);
dateField.addMinutes(1); //increment the counter until it reaches today's date and time
}
else{
break;
}
}
return minutes;
}())
Work hour difference
This formula returns the number of work hours between two specified dates.
kendo.format("{0:n}",
(function(){
var startHour = 9;
var endHour = 17;
var workDays = ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday"];
var minutes = 0;
var dateField = new Date(Date1__c); // This is the first date field in Salesforce
var todaysDate = new Date(Date2__c); // This is the second date field in Salesforce
// To specify today instead of Date2, use new Date(). For example:
// var todaysDate = new Date();
while(true) {
//compare Date1 with today
if(dateField.compareTo(todaysDate) < 0) {
// (…if…) ? (…then…) : (…else…)
// If the date is within the workDays array and within work hours then add 1, else 0
minutes += (workDays.indexOf(dateField.getDayName()) > -1
&& dateField.getHours() >= startHour
&& dateField.getHours() < endHour
? 1 : 0);
dateField.addMinutes(1); //increment the counter till we hit today's date/time
}
else{
break;
}
}
return minutes / 60 ;
}())
)
Work day difference
This formula returns the number of work days between two specified dates.
kendo.format("{0:n}",
(function(){
var startHour = 9;
var endHour = 17;
var workDays = ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday"];
var minutes = 0;
var dateField = new Date(Date1__c); // This is the first date field in Salesforce
var todaysDate = new Date(Date2__c); // This is the second date field in Salesforce
// To specify today instead of Date2, use new Date(). For example:
// var todaysDate = new Date();
while(true) {
//compare Date1 with today
if(dateField.compareTo(todaysDate) < 0) {
// (…if…) ? (…then…) : (…else…)
// If the date is within the workDays array and within work hours then add 1, else 0
minutes += (workDays.indexOf(dateField.getDayName()) > -1
&& dateField.getHours() >= startHour
&& dateField.getHours() < endHour
? 1 : 0);
dateField.addMinutes(1); //increment the counter till we hit today's date/time
}
else{
break;
}
}
return minutes / 60 / (endHour - startHour);
}())
)
-1 day from Day 1
This formula subtracts one day from a specified date.
kendo.format("{0:d}", new Date(Date1__c).addDays(-1))
-1 day from Day 1
This formula subtracts one day from a specified date.
kendo.format("{0:d}", new Date(Date1__c).addDays(-1))
+1 hour from Day 1
This formula adds one hour to a specified date.
kendo.format("{0:g}", new Date(Date1__c).addHours(1))
+1 month from Day 1
This formula adds one month to a specified date.
kendo.format("{0:d}", new Date(Date1__c).addMonths(1))
Hours difference
This formula computes the number of hours between two specified dates.
kendo.format("{0:n}", ((Date1__c - Date2__c) / (1000*60*60)) )
Days difference
This formula computes the number of days between two specified dates.
kendo.format("{0:n}", ((Date1__c - Date2__c) / (1000*60*60*24)))
Days difference UTC
This formula returns the number of days between two specified dates. Use this function to compare dates where one is in a date/time format (such as today's date) and the other is just a date field (with no time). Unless you want the decimal component of the partial day, you need to put both dates in the 12:00:00 a.m. format. The formula does this by subtracting the hours component, if it exists.
kendo.format("{0:n}",
(
function () {
var startDate = new Date(Date2__c);
var endDate = new Date(Date3__c);
var millisecondsPerDay = 24 * 60 * 60 * 1000;
// Make sure that both dates are at midnight. Salesforce date SF fields have a Date Offset built in.
// So we have to subtract from midnight and then move the date up to midnight of the next day.
if(startDate.getHours() > 0) {
//if the time is already @ midnight then don't adjust
startDate = startDate.addHours( -1 * startDate.getHours())
startDate = new Date(startDate.getFullYear(),startDate.getMonth(), startDate.getDay());
}
if(endDate.getHours() > 0) {
//if the time is already @ midnight then don't adjust
endDate = endDate.addHours( (-1 * endDate.getHours()));
}
return ((endDate - startDate ) / millisecondsPerDay);
}()
)
)
