PDA

View Full Version : time deduction



black_salami
03-14-2018, 12:52 AM
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
03-14-2018, 08:57 AM
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
03-14-2018, 09:21 AM
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
03-15-2018, 12:04 AM
column C is start of shift column D is end of shift