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