Total SUM Of One row for another Parent row but not children

Hi @Jon Perry Melink

The data you have in your column, 1h 30m, is going to be read as text by smartsheet. It cannot do math on text so the report will return 0.

If you were to separate your hours and minutes into separate columns, you could then sum the hours and minutes.

You could separate them using a formula and retain the original column if you want/need.

How to extract minutes and hours from your text column

If you have the column set as a Duration format, change it to Text/Number as Duration is useful for durations in days, as soon as you put text in there instead it will get messy.

Then add some new columns.

I assume your duration column could have 1 or 2 digits before the "h" and 1 or 2 digits before the "m". So have written a couple of formulas to find the numbers we need.

New column 1 - extract hours from your cell

This formula looks at the string in your Duration column, takes the characters to the left of the "h" and converts them into a value (rather than text):

=VALUE(LEFT(Duration@row, FIND("h", Duration@row) - 1))

I would wrap this in an IFERROR function to return a blank if there are no hours (rather than an error message):

=IFERROR(VALUE(LEFT(Duration@row, FIND("h", Duration@row) - 1)), "")

Or better still produce a 0 when there is an error (before converting the string to a value) so you can do math on this:

=VALUE(IFERROR(LEFT(Duration@row, FIND("h", Duration@row) - 1), "0"))

New column 2 - extract minutes from your cell

This formula looks at the string in your Duration column, takes the characters between the "h " and the "m" and converts them into a value:

=VALUE(MID(Duration@row, FIND("h ", Duration@row) + 2, FIND("m", Duration@row) - (FIND("h ", Duration@row) + 2)))

As above, I would incorporate an IFERROR:

=VALUE(IFERROR((MID(Duration@row, FIND("h ", Duration@row) + 2, FIND("m", Duration@row) - (FIND("h ", Duration@row) + 2))), "0"))

(if you ever have minutes without hours at the start then we would need a different formula)

Make both formula column formulas.

Example

Your new columns will look something like this:

Using the new columns

You could then combine these into one column for the duration in hours or duration in minutes and use that in your report. You can do this by combining the formula (which means you can delete the columns above) but to get started I think it would be best to simply refer to the two columns you have.

If you wanted to show the duration in hours it would be

=Hours@row + (Minutes@row / 60)

Or in minutes, it would be

=(Hours@row * 60) + Minutes@row

Then you can create reports like this:

Or

You might want to use the ROUND function to make this cleaner.

Basically, once you get your durations into numeric values, you can do almost anything!