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


Tags:

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!