Formula: Adding Time

Brittanyy

"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

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)

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)