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
Best Answer
-
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)
Answers
-
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)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!