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

CodePudding user response：

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"))

CodePudding user response：

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