time deduction

[black_salami]

Hello,

Can You please help me with formula for lunch break time deduction?

In attached file in "krata ECP" tab in E column i managed to make forumula to deduct 15 minutes if work time exceeeds 6 hour but i have problem doing the same for F column.

Will be very greatfull for any assiatnce.

Best regards

[MINCUS1308]

don't speak that language.
what are columns C and D? in and out?

This is the jist of whats going on:
'$A$2 = 0.01 'THE 15 MINUTE BREAK
'$K$6 = 8 'THE NUMBER OF HOURS IN THE WORKING DAY????

'COLUMN E FORMULA
=IF(OR(C12="",C12="off",C12="N",D12=""),

0,
IF(OR(C12="W",C12="C",C12="U",C12="M",C12="D"),

$K$6/24,

MOD(D12-C12,1)-$A$2*(MOD(D12-C12,1)>6/24

)))*24

'COLUMN F FORMULA
=IF(OR(C12="",D12="",C12="off",C12="N"),

0,

IF(OR(C12="W",C12="C",C12="U",C12="M",C12="D"),

$K$6/24,

IF(AND(C12<=TIME(23,0,0), D12>=TIME(7,0,0),D12<C12),

TIME(23,0,0)-C12+D12-$A$2-TIME(7,0,0),

IF(AND(C12<=TIME(7,0,0),D12<=TIME(23,0,0)),

D12-$A$2-TIME(7,0,0),

IF(AND(C12>=TIME(7,0,0),D12<=TIME(23,0,0),D12>C12),

D12-C12-$A$2,

IF(AND(C12>=TIME(7,0,0),OR(D12>=TIME(23,0,0),D12<=TIME(7,0,0))),

TIME(23,0,0)-C12,

0

))))))*24

[MINCUS1308]

The logic for column F is:

BREAK = 0.01 '$A$2
HOURS= 8 '$K$6

IF (C="" OR D = "" OR C = "off" OR D = "N")

MyValue = 0

ELSEIF (C="W" OR C="C" OR C="U" OR C="M" OR C="D")

MyValue = HOURS/24

ELSEIF (C<=23 AND D>= 7 AND D<C)

MyValue = 23-C+D-BREAK-7

ELSEIF (C<=7 AND D <= 23)

MyValue = D-BREAK - 7

ELSEIF (C>=7 AND D<=23 AND D>C)

MyValue = D-C-BREAK

ELSEIF (C>=7 AND (D>=23 OR D<=7))

MyValue = 23-C

ELSE

MyValue = 0

END IF

MyValue = MyValue * 24

[black_salami]

column C is start of shift column D is end of shift