Excel sheet:

"Q1. Please calculate the total working hours in C Column.

Instructions.

1. Please do not use any helping column.
2. Please deduct 1 hour break time if working hours are 8 or more than 8 hours
3. If working hours are less than 8 hours please deduct only 30 mins."

CodePudding user response：

For Microsoft 365 user-

=IFERROR(LET(x,MOD((--RIGHT(B2,5))-(--LEFT(B2,5)),1)*24,IF(x>=8,x-1,x-0.5)),B2)

For all version of excel-

=IFERROR(IF(MOD((--RIGHT(B2,5))-(--LEFT(B2,5)),1)*24>=8,MOD((--RIGHT(B2,5))-(--LEFT(B2,5)),1)*24-1,MOD((--RIGHT(B2,5))-(--LEFT(B2,5)),1)*24-0.5),B2)

For office Insiders or Current Preview channel user.

=BYROW(B2:B6,LAMBDA(a,LET(x,MOD((CHOOSECOLS(TEXTSPLIT(a,"-"),2)-CHOOSECOLS(TEXTSPLIT(a,"-"),1)),1)*24,IF(x>=8,x-1,x-0.5))))

CodePudding user response：

Something like:

Formula in B1:

=LET(X,RIGHT(A1,5) (--RIGHT(A1,5)<--LEFT(A1,5))-LEFT(A1,5),IFERROR(IF(X>=TIME(8,,),X-TIME(1,,),X-TIME(,30,)),A1))