Specify Duration for Time Gaps Between Dates (Custom Code)

Smart CRM & Sales Hub Tips, Tricks & Best Practices
, ,
1

The caluculation property’s Time between function%20%3D%201699320014000-,time_between,-Get%20the%20time) measures the gap between two date/times, and the displayed value will be rounded to the closest whole number of the unit based on specific thresholds, as outlined in the knowledge base.

I tested a custom-coded workflow action that calculates how many hours, days, months, and years have passed between two properties or since a date property of your choice.

Preparations

1. Properties to include in the action

  • start_date_property: A date or date/time property that stores the earlier date.
  • end_date_property (optional): A date or date/time property representing the later date.
    • If omitted, you can adjust the code to use the current date/time.

2. Data Output

  • hours_passed (Number)
  • days_passed (Number)
  • months_passed (Number)
  • years_passed (Number)

3. (Optional) Create matching number properties if you want to store the output for reports, lists, or other workflows.

  • If you do, please make sure to add an “Edit Record” action after your custom code to save the calculated values into properties.
  • If you only need the generated values inside the same workflow, you don’t need to have the properties.

Codes for the action

exports.main = async (event, callback) => {
 try {
 // Get the date properties from the input fields
 const startDateProperty = event.inputFields['start_date_property'];
 const endDateProperty = event.inputFields['end_date_property']; // only needed If you'd like to caluculate the time gap between two properties.
 // const endDateProperty = new Date().getTime(); // Get current date and time in UNIX timestamp (milliseconds)

 console.log("Raw Start Date Property:", startDateProperty);
 console.log("Raw End Date Property:", endDateProperty);

 // Convert the UNIX timestamps (milliseconds) to Date objects
 const startDate = new Date(parseInt(startDateProperty)); // Convert start date
 const endDate = new Date(parseInt(endDateProperty)); // Convert end date

 console.log("Converted Start Date:", startDate);
 console.log("Converted End Date:", endDate);

 // Ensure endDate is after startDate
 if (endDate < startDate) {
 console.error("End date is before start date.");
 return;
 }

 const differenceInSeconds = Math.floor((endDate - startDate) / 1000); // Convert difference to seconds

 // Calculate Hours Passed
 const hoursPassed = Math.round(differenceInSeconds / 3600); // 60*60 = 3,600 seconds/hour
 console.log("Hours Passed:", hoursPassed);

 // Calculate Days Passed
 const daysPassed = Math.round(differenceInSeconds / 86400); // 24*60*60 = 86,400 seconds/day;
 console.log("Days Passed:", daysPassed);

 // Calculate Months Passed
 let monthsPassed = (endDate.getFullYear() * 12 + endDate.getMonth()) - (startDate.getFullYear() * 12 + startDate.getMonth());

 // If end date's day is before start date's day, subtract 1 month
 if (endDate.getDate() < startDate.getDate()) {
 monthsPassed -= 1;
 }
 console.log("Months Passed:", monthsPassed);

 // Calculate Years Passed
 let yearsPassed = endDate.getFullYear() - startDate.getFullYear();

 // If end date is before the start date's month and day, subtract 1 year
 if (endDate.getMonth() < startDate.getMonth() || (endDate.getMonth() === startDate.getMonth() && endDate.getDate() < startDate.getDate())) {
 yearsPassed -= 1;
 }
 console.log("Years Passed:", yearsPassed);

 // Return values to copy to number properties in the later action
 callback({
 outputFields: {
 hours_passed: hoursPassed,
 days_passed: daysPassed,
 months_passed: monthsPassed,
 years_passed: yearsPassed
 }
 });
 } catch (error) {
 console.error("Error in calculating days, months, and years:", error);
 }
};

console.log("End of Action")