Sign in
x
☰

Date/time calculations

Updated at 2020-01-13 15:41:11 UTC

In the Date/time values tutorial, we demonstrated a few basic ways to parse and analyze dates. This tutorial shows how to do two common calculations with dates:

  • Combine month and year values into a single date
  • Time interval calculations

Convert separate date values to strings 

Surveys may elicit dates, not as dates, but as separate questions (e.g. year, month, day). We can use data process to convert these into date strings that Protobi can recognize. 

Consider a question that asked for month and year separately: 

Note: The month values in q1_2 are actually numeric, and month names are the format.

Create the process

To convert month and year from the above example into date strings, create a data process:

  • Go to the data tab of your project 
  • Create a new process (or edit an existing process) 
  • Set the process as primary
  • Press "Edit/Run" to get to process code page
  • Paste in the code (from below)
  • Press run, and save.

Convert to date strings code

The code starts with a simple return rows, which just returns an array containing your dataset. The program iterates over this array and creates a new data column for row.q1 that concatenates the month and year values into strings.

We used an existing key "row.q1" as the element that will contain the string values. However, you can make up a new key which will write a new element containing the date strings into existence. 

Expand to see the code

For the date fields enter:

  • key: Parent group's key can be used or use a new key 
  • month: Month element's key
  • year: Year element's key
var rows = data.main;


// Convert Month/Year pairs to date strings
var date_fields = [
  {key:  "q1",   month: "q1_1", year: "q1_2"}, // combine q1 month and year
  {key:  "q2",   month: "q2_1", year: "q2_2"}, // combine q2 month and year
  {key:  "q3",   month: "q3_1", year: "q3_2"}, // combine q3 month and year


]


rows.forEach(function(row){
    _.each(date_fields, function(entry) {
        if (row[entry.year]) {
            var date_str = row[entry.year] + '-' + row[entry.month].padStart(2, '0') + '-01' + 'T12:00:00'
            row[entry.key] = date_str//moment(date_str, 'YYYY-MM')
        }
    })


return rows



Open your project. If it's already open, refresh the page. Keys that didn't previously exist, but were written into existence in the code should be in fields. 

Once you find the element containing the date strings, in element properties change type from "empty" to "date" (seen here). 

Result

Below, we see q1 is populated with the resulting date strings. The dates are not in ISO date format, but you can customize date formats in JSON.

You can hide, remove, or delete the separate month and year child elements . 


Transform dates

For groups with type "date" you can transform to a unit of time (e.g. year, quarter, etc.): 

Below, we transformed q1 to year and hid children:

Calculate time between dates

We can also use process code to calculate time intervals. In the example below, we want to calculate the time between dates entered in q3 and q4. 

Time interval code

Set up the data process (see steps above) but paste in the time interval code seen below.

You can delete or add rows of code as necessary, depending on how many time intervals you need to calculate. 

For the time fields enter:

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

 Press run, and save. 

Time interval in months

var rows = data.main;
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);
}


function getMonthsBetween(end, start) {
    if (end && start) return  getAbsoluteMonths(end) - getAbsoluteMonths(start)
    else return null
}
// Convert date strings to time intervals
var date_intervals = [
    {key: "q3_q4", start: "q3", end: "q4", unit: 'month'}, // q3 date to q4 date
    {key: "q4_q5", start: "q4", end: "q5", unit: 'month'}, // q4 date to q5 date


]


// convert dates to intervals
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 


Result

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. 

In this tutorial, we discussed two common types of date/time calculations that can be done in Protobi. Your Protobi project may need different or more complex time calculations not yet written here. Contact support@protobi.com and we'll help you get setup for your case.