Formula: Adding Time

"Hi there,

Just to provide some context, I'm currently working on creating a metrics sheet, and I'm looking to aggregate all the hours based on the Legal Team Member."

Formula's I have tried:

=HOURS(SUMIFS({SLA Hours}, {Legal Team Member}, "John Doe") * 24)

=TEXT(INT(SUMIFS({SLA Hours}, {Legal Team Member}, "John Doe") * 24 * 60) / 1440, "D \d\a\y\s, H \h\o\u\r\s, M \m\i\n\u\t\e\s, S \s\e\c\o\n\d\s")

References:

SLA Hours is referencing the " dd.hh.mm.ss" column

Legal Team Member is referencing the the " legal Team Member"column

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

You will have to break the numbers out into actual numbers. You can use separate columns, or you can use a single column and convert it all into a common denomination. From there you would be able to sum everything up and then convert the total back into your text format.

Into separate columns would look like this...

Days:

=VALUE(LEFT([dd:hh:mm:ss]@row, 2))

Hours:

=VALUE(MID([dd:hh:mm:ss]@row, FIND("!", SUBSTITUTE([dd:hh:mm:ss]@row, " ", "!", 2), 3))

Minutes:

=VALUE(MID([dd:hh:mm:ss]@row, FIND("!", SUBSTITUTE([dd:hh:mm:ss]@row, " ", "!", 4), 3))

Seconds:

=VALUE(RIGHT([dd:hh:mm:ss]@row, 2))

Using a single column and converting it into the number of minutes would look like this:

=(VALUE(LEFT([dd:hh:mm:ss]@row, 2)) * 1440) + (VALUE(MID([dd:hh:mm:ss]@row, FIND("!", SUBSTITUTE([dd:hh:mm:ss]@row, " ", "!", 2), 3)) * 60) + VALUE(MID([dd:hh:mm:ss]@row, FIND("!", SUBSTITUTE([dd:hh:mm:ss]@row, " ", "!", 4), 3)) + (VALUE(RIGHT([dd:hh:mm:ss]@row, 2)) / 60)

All comments

Paul Newcome

Into separate columns would look like this...

Days:

=VALUE(LEFT([dd:hh:mm:ss]@row, 2))

Hours:

=VALUE(MID([dd:hh:mm:ss]@row, FIND("!", SUBSTITUTE([dd:hh:mm:ss]@row, " ", "!", 2), 3))

Minutes:

=VALUE(MID([dd:hh:mm:ss]@row, FIND("!", SUBSTITUTE([dd:hh:mm:ss]@row, " ", "!", 4), 3))

Seconds:

=VALUE(RIGHT([dd:hh:mm:ss]@row, 2))

Using a single column and converting it into the number of minutes would look like this:

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.