CODEWITHSUNDEEP
google-sheets-formula
mau
mau

Reputation: 185

Get hours from schedule

How can I get the total working hours scheduled with this format? Expected output should be 46.

enter image description here

Upvotes: 1

Views: 45

Answers (1)

tomf
tomf

Reputation: 535

Not the prettiest things, but here are two ways to tackle it

=ARRAYFORMULA(
  SUM(
   IFERROR(
    INDEX(SPLIT(TRANSPOSE(A2:G2)," - ",FALSE,TRUE),0,2)-
    INDEX(SPLIT(TRANSPOSE(A2:G2)," - ",FALSE,TRUE),0,1)))*
  24)

or

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

Either way, we extract the second values then subtract the first values from that, sum it, and multiply it by 24.

For overnight shifts, try this

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

in [hh]:mm:ss

=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+)")))),
   "[hh]:mm:ss"))

Upvotes: 1

Related Questions