Calculate time between dates

Updated at November 19th, 2020

This tutorial shows how to programmatically calculate the time between date values in your survey.  

Example

In the example below, we want to calculate the time interval between dates entered for q3 and q4


Data processing code

For the time fields enter:

  • key: Create a key that can be easily found and identified (e.g. q3_q4)
  • start: Start date/time (e.g. q3)
  • end: End date/time (e.g. q4)
  • unit: Unit of measurement for the interval (e.g. months, weeks)

 

Difference in months

var rows = data.main;

// Enter key (new variable name for the time intervals) and start/end variables
var date_intervals = [
    {key: "q3_q4", start: "q3", end: "q4", unit: 'months'}, // q3 date to q4 date
    {key: "q4_q5", start: "q4", end: "q5", unit: 'months'}, // q4 date to q5 date

]

//Define the function that specifies format for the difference between dates
function getAbsoluteMonths(dt) {
  var momentDate = moment(new Date(dt))
  var months = Number(momentDate.format("MM"));
  var years = Number(momentDate.format("YYYY"));
  return months + (years * 12);
}

//Define the function that calculates the difference between dates
function getMonthsBetween(end, start) {
    if (end && start) return  getAbsoluteMonths(end) - getAbsoluteMonths(start)
    else return null
}

// Convert date strings to time intervals
//Below the start/end date format is YYYY-MM. The dates in your project may be in a different format, adjust accordingly.
rows.forEach(function(row) {
    
    _.each(date_intervals, function(entry) {
        if (row[entry.start] && row[entry.end]) {
            var start = moment(row[entry.start], 'YYYY-MM')
            var end = moment(row[entry.end],'YYYY-MM')
            row[entry.key] =  getMonthsBetween(end, start)
        }
    })
    
})


return rows


Difference in weeks

var rows = data.main;

// Enter key (new variable name for the time intervals) and start/end variables
var date_intervals = [
    {key: "q3_q4", start: "q3", end: "q4", unit: 'weeks'}, // q3 date to q4 date
    {key: "q4_q5", start: "q4", end: "q5", unit: 'weeks'}, // q4 date to q5 date

]

//Define the function that specifies format for the difference between dates
function getAbsoluteWeeks(dt) {
    var momentDate = moment(new Date(dt))
    var weeks = momentDate.weeks()
    var dates = Number(momentDate.format("DD"));
    var months = Number(momentDate.format("MM"));
    var years = Number(momentDate.format("YYYY"));
    return weeks + (years * 52);
}

//Define the function that calculates the difference between dates
function getWeeks(end, start) {
    if (end && start) return  getAbsoluteWeeks(end) - getAbsoluteWeeks(start)
    else return null
}

// Convert date strings to time intervals
//Below the start/end date format is MM/DD/YYYY. The dates in your project may be in a different format, adjust accordingly.
rows.forEach(function(row) {
    
    _.each(date_intervals_weeks, function(entry) {
        if (row[entry.start] && row[entry.end]) {
            var start = moment(row[entry.start], 'MM/DD/YYYY')
            var end = moment(row[entry.end],'MM/DD/YYYY')
            row[entry.key] =  getWeeks(end, start)
        }
    })
    
})


return rows 


Resulting date/time intervals

Open your project (or refresh the page). The interval elements should be in fields because they were written into existence in the code. You can use the tree to search for elements if you're having trouble locating them. 



Reminder: For data processes, "Save" and "Run" the process after you are done editing the code view. To use the result of the process as the primary data for the project, you will need to set it as "Primary".
Data processes are specific to each project, and your code may not look identical to our example.

Your Protobi project may need different or more complex time calculations not yet written in our tutorials. Contact support@protobi.com and we'll help you get setup for your case.

Was this article helpful?