How I calculate HH:mm
Here's how I figured out HH:mm. I welcome all simplification suggestions.
The combined formula in A38 is this monstrosity (but it works):
=IFERROR(IF(INT(SUMIFS({Duration}, {CURWK}, 1, {Event Type}, A$37) / 60) < 10, "0" + INT(SUMIFS({Duration}, {CURWK}, 1, {Event Type}, A$37) / 60), INT(SUMIFS({Duration}, {CURWK}, 1, {Event Type}, A$37) / 60)) + ":" + IF(MOD(SUMIFS({Duration}, {CURWK}, 1, {Event Type}, A$37), 60) < 10, "0" + MOD(SUMIFS({Duration}, {CURWK}, 1, {Event Type}, A$37), 60), MOD(SUMIFS({Duration}, {CURWK}, 1, {Event Type}, A$37), 60)), "00:00")
----------------------------------------------------
{CURWK} - checkbox in data source indicating whether the event is "this" week
{Event Type} - single-select dropdown column in data source indicating the type of event
{Duration} - text/number column in data source with #minutes for each event
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives