Fórmula do Excel: descubra o tempo gasto em intervalos de tempo específicos

Question

Tentando elaborar uma planilha de horas extras um pouco mais complexa aqui:

O funcionário é pago:

• 1x taxa horária durante a semana (seg-sex) das 08:30 às 17:30
• taxa horária de 1,5x durante a semana (seg-sex) antes das 08:30 ou depois das 17:30
• taxa horária de 1,5x aos sábados
• 2x taxa horária aos domingos e feriados

Layout da folha:

|Date   |Day    |Slip No    |Name   |Destination    |Start Time |End Time   |Total Hours Worked |Basic Hours    |OT @ 1.5   |OT @ 2.0

Estou procurando fórmulas para colunas

• [H] = horas trabalhadas
• [I] = horas básicas
• [J] = OT @ 1,5
• [K] = OT @ 2.0

Estou apenas procurando por uma representação hh: mm do que foi trabalhado; então não há necessidade de um campo de 'taxa horária'.

[H] =MOD(G6-F6,1)
[I] =IF(F6<G6,MIN(G6,Data!F2)-MAX(F6,Data!E2),MAX(0,Data!F2-F6)+MAX(0,G6-Data!E2))
[J] =H6-I6
[K] =IF(OR(WEEKDAY($A6)=1,ISERROR(VLOOKUP($A6,tblPublicHolidays,1,FALSE))=FALSE),$H6,0)

[H] = trabalhando;

[K] = trabalhando;

[I] / [J] são o problema e eu sinto que, se conseguisse fazer o trabalho, também teria a solução para J. J é apenas "total de horas" menos "horas básicas".

tblPublicHolidays é uma lista de feriados que temos.

Dados! E2 = hora de início - por exemplo: 08:30

Dados! F2 = hora final - ou seja: 17:30

Para o propósito desta demo, esta primeira linha (6) tem o valor de uma hora de início como 18:30 e hora de término como 18:45. Eu tenho a fórmula [I] de link mas não parece estar trabalhando por estas razões:

• [I] aparece como um monte de hashes (#) se eu tiver o formato como 'Tempo'
• Se eu alterar [I] para o formato Number, ele será exibido como -0,04
• [J] parece ter sempre 1 hora a mais do que deveria (neste exemplo 1:15 em vez de 0:15)

Answer 1

Obrigado pela ajuda, mas foi assim que eu acabei (supondo que a linha inicial seja 6):

A6 (DATE)
B6 (DAY) =IF(A6<>"",TEXT(A6,"dddd"),"")
F6 (START TIME)
I6 (END TIME)
L6 (HOURS WORKED) =IF(AND(F6>0,I6>0),MOD(I6-F6,1)*24,"")
M6 (BASIC HOURS) =IF(AND(F6>0,I6>0),IF(AND(WEEKDAY($A6)>1,WEEKDAY($A6)<7),IF(F6 < I6,MAX(0,MIN(I6,upper)-MAX(F6,lower))*24,MAX(0,upper - F6)+MAX(0,I6 - lower)*24),0),"")
N6 (Overtime @ 1.5) =IF(AND($F6>0,$I6>0),IF(AND(WEEKDAY($A6)>1,ISERROR(VLOOKUP($A6,tblPublicHolidays,1,FALSE))=TRUE),(L6-M6),0),"")
O6 (Overtime @ 2.0) =IF(AND($F6>0,$I6>0),IF(OR(WEEKDAY($A6)=1,ISERROR(VLOOKUP($A6,tblPublicHolidays,1,FALSE))=FALSE),$L6,0),"")