Duration Conversion with a Custom Function in Google Sheets

Introduction

Have you ever dealt with durations in your Google Sheets, only to struggle with converting them into a numeric format for further calculations or analysis? Fortunately, a handy solution can streamline this process and save time and effort. In this blog post, we'll explore how to easily use a custom function in Google Sheets to convert durations to a numeric format. Whether you have a duration in days, hours, minutes, and seconds or a combination of these units, this custom function will handle the conversion. So, let's dive in and discover how to simplify duration conversion with a custom function in Google Sheets!

Imagine you have a duration in a cell just as a string of text, say in cell B3, and you want to convert it to a numeric duration in cell C3. With the help of this custom function, you can achieve this conversion seamlessly. Let's explore the steps involved and see how this custom function can be implemented.

In order to achieve this, we need to create our very own custom function to use within Google Sheets; this is possible by using Google Apps Script. Don’t worry if you’re new to Apps Script, I’ll walk you through the steps to get everything set up.

Google Apps Script

Head up to the main menu and choose Extensions > Apps Script.

This will open a new window in your browser with a boilerplate function template. You should delete this function in the main canvas, as we will replace that with the full script.

You should copy and paste the script below into the Apps Script window. After you’ve pasted this in, save the file by clicking the save icon, or Cmd+S or Ctrl+S and name the project to something meaningful.

/**
 * Duration entered as a string, such as 4d 18h 26m 24s to convert to a duration format, such as 114:26:24 - By Ant @ Spreadsheetwise.com
 * @constructor
 * @param {string} input - Select a cell with duration as a string to convert to a duration format - "HH:mm:ss"
 * @customfunction
 */
 
function CONVERTDURATION(input) {
  var days = 0;
  var hours = 0;
  var minutes = 0;
  var seconds = 0;

  var regexPattern = /(\d+)(d|h|m|s)/g;
  var match;
  while ((match = regexPattern.exec(input)) !== null) {
    var value = parseInt(match[1]);
    var unit = match[2];

    if (unit === 'd') {
      days = value;
    } else if (unit === 'h') {
      hours = value;
    } else if (unit === 'm') {
      minutes = value;
    } else if (unit === 's') {
      seconds = value;
    }
  }

  var totalSeconds = days * 24 * 60 * 60 + hours * 60 * 60 + minutes * 60 + seconds;
  var duration = totalSeconds / 60 / 60 / 24;
  
  return duration;
}

Once you’ve done that, click the Run button to run the script for the first time. This will add the custom function to your Google Sheets, ready to use.

If everything worked as expected, you should see the Execution log at the bottom with a notice showing it was completed.

Using the Custom Function

Next, head back to your Google Sheet and enter some text in cell B3 like this: 6h 7m 35s

You may get some text like this from automatically generated reports (This is what prompted me to write this script since I downloaded a report where the durations were exported as a text string), which is why this is a handy custom function to have since it will allow you to convert it to a numeric duration that you can run calculations on.

In cell C3, enter your new custom function by typing =CONVERTDURATION. This will bring up some suggestions; you should see yours there too.

For the input, you will select cell B3 or wherever you have the duration as a string.

Now close the parenthesis and hit enter. You’ll see the cell briefly loading as the Apps Script runs, and then you’ll get the duration back as a numeric value in the format HH:mm:ss.

With this converted numeric duration, you can run calculations such as summing durations.

How it works

If you’re interested in what’s happening under the hood, I’ll break this down here.

function CONVERTDURATION(input) {
  var days = 0;
  var hours = 0;
  var minutes = 0;
  var seconds = 0;

The function CONVERTDURATION(input) takes an input parameter, which represents the duration string you want to convert. First, it initializes variables days, hours, minutes, and seconds to zero.

var regexPattern = /(\d+)(d|h|m|s)/g;
  var match;
  while ((match = regexPattern.exec(input)) !== null) {
    var value = parseInt(match[1]);
    var unit = match[2];

A regular expression pattern is defined to match numeric values followed by units like "d" (days), "h" (hours), "m" (minutes), or "s" (seconds). The exec method is used to iterate over all matches found in the input string.

For each match, the numeric value is extracted using parseInt and stored in the value variable, and the unit is stored in the unit variable.

if (unit === 'd') {
      days = value;
    } else if (unit === 'h') {
      hours = value;
    } else if (unit === 'm') {
      minutes = value;
    } else if (unit === 's') {
      seconds = value;
    }
  }

Based on the unit value, the corresponding variable (days, hours, minutes, or seconds) is updated with the extracted value. This allows us to accumulate the total duration components.

var totalSeconds = days * 24 * 60 * 60 + hours * 60 * 60 + minutes * 60 + seconds;
  var duration = totalSeconds / 60 / 60 / 24;
  
  return duration;
}

The total duration is calculated by converting each component (days, hours, minutes, and seconds) to seconds and summing them together. Then, the totalSeconds value is divided by 60 * 60 * 24 to convert it to days.

Finally, the resulting duration in days is returned by the function.

Conclusion

By leveraging the power of custom functions in Google Sheets, you can simplify the conversion of durations to numeric formats, enabling you to perform further calculations and analysis efficiently. Say goodbye to manual conversions and embrace this convenient solution for handling durations in your spreadsheets.

Give this a go on your own sheets, or make a copy of this example by clicking this link.

Convert Duration with Apps Script