How to Use ISO 8601 Durations in Excel & Google Sheets

A practical guide for spreadsheet users

1. The Challenge of ISO 8601 Durations in Spreadsheets

While spreadsheet applications like Microsoft Excel and Google Sheets are powerful tools for data analysis, they don't have built-in functions to natively understand or parse ISO 8601 duration strings like P1Y2M3DT4H5M6S. If you have a column of durations like PT1H30M, you can't directly perform calculations on them.

This can be a significant challenge when working with APIs, databases, or data sources that use this standard format. This guide provides practical formulas and techniques to extract meaningful data from these strings within your spreadsheet.

2. Getting Started: Extracting Components with Formulas

You can use a combination of string manipulation functions to pull out the numerical values for hours, minutes, and seconds from ISO 8601 duration strings. However, this approach has limitations and can be complex.

Google Sheets Example:

If you have the duration PT1H30M in cell A1, you can extract the components like this:

// Extract Hours (H)
=IFERROR(REGEXEXTRACT(A1, "T(\d+)H"), 0)

// Extract Minutes (M)
=IFERROR(REGEXEXTRACT(A1, "H(\d+)M"), REGEXEXTRACT(A1, "T(\d+)M"))

// Extract Seconds (S)
=IFERROR(REGEXEXTRACT(A1, "M(\d+)S"), REGEXEXTRACT(A1, "T(\d+)S"))

These formulas are complex and can be brittle. They don't handle all edge cases gracefully, such as durations with years, months, or weeks.

Excel Example:

Excel makes this even more difficult without built-in regex functions. You would need to use a combination of FIND, MID, and IFERROR, which can become very long and hard to manage:

// Extract Hours (simplified example)
=IFERROR(MID(A1,FIND("T",A1)+1,FIND("H",A1)-FIND("T",A1)-1),0)

This approach becomes increasingly complex when dealing with multiple components and edge cases. As you can see, this method gets complicated quickly and doesn't handle all ISO formats.

3. Advanced Solution: Custom Apps Script for Google Sheets

For a more powerful and reusable solution in Google Sheets, you can create a custom function using Apps Script. This function will handle the conversion automatically and can process complex duration formats that include years, months, weeks, days, hours, minutes, and seconds.

1. Open the Script Editor: In your Google Sheet, go to Extensions > Apps Script.
2. Paste the Code: Replace any boilerplate code with the following script and click the "Save project" icon. 

/**
* Converts an ISO 8601 duration string to total seconds.
* @param {string} duration The ISO 8601 duration string (e.g., "PT1H30M").
* @return {number} The total duration in seconds.
* @customfunction
*/
function ISO_DURATION_TO_SECONDS(duration) {
  if (typeof duration !== 'string') {
    return null;
  }
  
  const regex = /P(?:(\d+)Y)?(?:(\d+)M)?(?:(\d+)W)?(?:(\d+)D)?T(?:(\d+)H)?(?:(\d+)M)?(?:(\d+(?:\.\d+)?)S)?/;
  const matches = duration.match(regex);
  
  if (!matches) {
    return null; // Not a valid duration format
  }
  
  const years = parseFloat(matches[1] || 0);
  const months = parseFloat(matches[2] || 0);
  const weeks = parseFloat(matches[3] || 0);
  const days = parseFloat(matches[4] || 0);
  const hours = parseFloat(matches[5] || 0);
  const minutes = parseFloat(matches[6] || 0);
  const seconds = parseFloat(matches[7] || 0);
  
  // Note: This is a simplified conversion and does not account for the variable length of months and years.
  // It's best for durations of days or less.
  return (years * 31536000) + (months * 2628000) + (weeks * 604800) + (days * 86400) + (hours * 3600) + (minutes * 60) + seconds;
}

3. Use the Function: You can now use this function in any cell: =ISO_DURATION_TO_SECONDS(A1). This will give you the total duration in seconds, which you can then easily use in calculations.

This solution handles complex duration formats like P1Y6M3DT4H30M (1 year, 6 months, 3 days, 4 hours, 30 minutes) that would be extremely difficult to parse with basic spreadsheet formulas.

4. The Easiest Solution: A No-Code Converter

While spreadsheet formulas and custom scripts are powerful, they require setup, debugging, and maintenance. For a fast, reliable, and error-free conversion, the simplest method is to use a dedicated online tool.

Our converter is designed to handle these complexities for you instantly: