The Duration data type is for representing a period of time, like the number of days elapsed between starting and ending dates.

Durations may be calculated off of other Date cells, Date & time cells, or data and formula inputted into a Duration cell. Duration data types can be used to show the time elapsed between a task's beginning and ending, to calculate the time remaining before upcoming deadlines, or used to figure out how quickly bugs need to be eliminated to hit your go-live date. Duration isn't just time elapsed between two dates or times, but can be calculated using any formula.

Configuration Options

Strict

Like other data types, Duration data types can have Strict enabled. Selecting the Strict checkbox for Duration cells will restrict users to input only numeric characters representation date or time durations. This means that formulas to calculate duration will need to inputted before setting Strict or the outputs of cells will need to be copy and pasted into duration cells. 

/note/ Currently, formulas are not allowed in strict Duration cells. When there are existing formulas in a column, cell, or range of cells and you change to strict, those formulas will be converted to the output value. You can always revert by selecting Undo from the top left corner of the toolbar, or by using the keyboard shortcut Ctrl + Z (CMD + Z for Mac users).

Format

Like Date data types, Duration data types have a number of custom formatting options. Shown above, these options allow you to display duration in days (2d), hours (1d 13h), or down to the second (37:26:24).

Calculating Duration

To calculate duration between multiple dates, you will need to input a formula into the column, cell, or range of cells you would like to calculate the duration for. For example, let's say we have two Date columns: one with the date a bug was submitted, and one with the date a bug was fixed.

To find out how long it took to fix the bug, we can create a third column with the Duration data type and subtract the submitted value from the fixed value. The result in the Duration column will be expressed in the format chosen when configuring the column, as detailed above.

Duration data types often utilize Date or Date & time data type. Check out our articles on those articles, or read on to learn more about the rest of Spreadsheet.com's Data Types.