Summarizing Time in a Report

KFont42

I have a master Smartsheet that lists every course we assign in our Learning Management System, the course length, and who all it is assigned to.

I then have several reports set up so I can filter the courses by Service Line, Level of Care, New Hire, etc. It is Grouped by Due Date.

This then feeds into a nice Dashboard that staff have read only access to.

I want the report to also add up all of the course lengths by both due date (so, all of the hours and minutes for Q1, Q2, etc.), and then overall for the year. I want it in hours and minutes.

Right now, in my Smartsheet, the course length is in minutes, and then I have a helper column that converts it into a nice hour and minutes formula. So, for example, the “Time in Minutes” column says “78,” and the “Course Time” column says “1 h 18 m.” What I cannot seem to do is add that helper column up. It gives me zero. And I also can’t enter formulas into Reports (as far as I can tell) to just use the minutes and convert it into that nice format.

I’ve played with the Smartsheet Summary fields, but I don’t think that’s going to give me what I want either – not without having to create a separate Summary Report and somehow build that into my Dashboard.

Have I gone as far as I can go, as far as you know?

Paul Newcome

You have taken it about as far as it can go. Any formula that outputs either #H #M or HH:MM is going to be treated as a text string which in turn cannot be used as a number.

KFont42

I think I can probably solve a chunk of the problem if I list the course duration as HH:MM instead of "1 h 17 m," but I'm not sure of the best way to do that or what to do with it from there to get it to summarize.

Paul Newcome

You have taken it about as far as it can go. Any formula that outputs either #H #M or HH:MM is going to be treated as a text string which in turn cannot be used as a number.