Change format to hh:mm:sss

Question

Can I adapt this formula to make the result into hh:mm:ss format instead of just a raw number?

    =ARRAYFORMULA(
      SUM(
       IFERROR(
        IF(
         --REGEXEXTRACT(A2:G2,"- (\d+:\d+)")<(--REGEXEXTRACT(A2:G2,"^(\d+:\d+)")),
          1+REGEXEXTRACT(A2:G2,"- (\d+:\d+)")-
          REGEXEXTRACT(A2:G2,"^(\d+:\d+)"),
          REGEXEXTRACT(A2:G2,"- (\d+:\d+)")-
          REGEXEXTRACT(A2:G2,"^(\d+:\d+)")))*
       24))

score 1 · Accepted Answer · answered Jun 15 '22 at 22:16

1

try:

=ARRAYFORMULA(TEXT(SUM(IFERROR(IF(
 --REGEXEXTRACT(A2:G2,"- (\d+:\d+)")<(
 --REGEXEXTRACT(A2:G2,"^(\d+:\d+)")),
 1+REGEXEXTRACT(A2:G2,"- (\d+:\d+)")-
   REGEXEXTRACT(A2:G2,"^(\d+:\d+)"),
   REGEXEXTRACT(A2:G2,"- (\d+:\d+)")-
   REGEXEXTRACT(A2:G2,"^(\d+:\d+)")))*24), "[hh]:mm:ss"))

answered Jun 15 '22 at 22:16

player0

124,011
12
67
124

I get 00:00:00 instead of the actual number – mau Jun 15 '22 at 22:30
@mau can you share a copy of your sheet? – player0 Jun 15 '22 at 22:30
I cant since I have sensitive information but I posted a ss of the example – mau Jun 15 '22 at 22:33
@mau hard to tell for sure... try `hh:mm:ss` instead of `[hh]:mm:ss` – player0 Jun 15 '22 at 22:36
I've got the same result – mau Jun 15 '22 at 23:16

SputnikDrunk2 · Answer 2 · 2022-06-16T01:53:50.030

Suggestion

Based on your question & on your previous post, you want to sum up the time from range A2:G2 OR AR2:AX2 (as seen on your sample screenshot) in hh:mm:ss format. Perhaps you can try this tweaked function below:

=ARRAYFORMULA(TEXT(SUM(IFERROR(TIMEVALUE(TRANSPOSE(
{
ARRAYFORMULA(IFNA(REGEXEXTRACT(AR2:AX2,"- (\d+:\d+)"),0)),
ARRAYFORMULA(IFNA(REGEXEXTRACT(AR2:AX2,"^(\d+:\d+)"),0))
}
)))), "[hh]:mm:ss"))

This function was derived from an answer on this quite similar post:

Change format to hh:mm:sss

2 Answers2

Suggestion

Demonstration