Este fórum está sendo desativado

[DaltonSilva]

Bom dia, mestres. Gostaria de um auxílio por parte dos senhores.
É o seguinte, estou fazendo a consolidação do banco de horas dos funcionários da empresa, porém, estou com uma dificuldade com relação aos momentos em que o funcionário fica de sobreaviso, tendo em vista a contabilização de horas na proporção 1 x 3 (1h trabalhada para cada 3h de sobreaviso)
A base de dados é a que está em anexo.
Os serviços são, em regra, de 24h ininterruptos, podendo ultrapassar este período.
O sobreaviso pode ser durante à noite das 20h de um dia às 8h do dia subsequente, ou das 8h às 20h do mesmo dia. Com isso, se não houver acionamento que enseje a quebra do sobreaviso, o funcionário é abonado em 4h no seu banco de horas (1 x 3).
Onde estou querendo chegar é o seguinte. Criar uma fórmula que identifique esses períodos de sobreaviso, seja no mesmo dia, ou de um dia para o outro e faço o cálculo da diferença de horas trabalhadas para que se divida o resultado por 3.
Infelizmente, não estou conseguindo chegar a uma fórmula, ou, de repente, em uma forma de se fazer isso.
PS.: Na planilha em anexo não há qualquer célula de cálculo pois o sistema faz isso automaticamente e apresenta a planilha para download. Infelizmente o sistema, ainda, não disponibiliza a função de registrar o sobreaviso e isso é feito manualmente.
Como houve uma demanda de consolidação do banco de julho de 2017 até fevereiro de 2021, e antes de 2019 não estava havendo controle, estamos com esta demanda para resolver. E fazer isso para 40 funcionários pode ser que seja extenuante caso não haja o mínimo de automação.
Desde já agradeço e estou à disposição para sanar dúvidas de quem se dispuser a ajudar.

[DaltonSilva]

Pessoal. Ainda não encontrei uma maneira de resolver essa questão. Quem puder ajudar eu agradeço.

Para facilitar a visualização, segue uma imagem de como é apresentada a planilha.

[Estevaoba]

Bom dia.
O ideal seria você colocar o resultado esperado em algumas linhas para testar possíveis soluções.
Mas resolvi arriscar com esta fórmula, referente à linha 3.
Cole em qualquer coluna vazia e copie para baixo.

Código: Selecionar todos

=SE(OU(E(TEMPO(MOD(A3;1);0;0)<=8/24;B3-A3>=12/24);E(MOD(A3;1)>8/24;B3-A3>=12/24));(B3-A3)/3;0)

Good luck!

[DaltonSilva]

Meu amigo. Desde já deixo meus agradecimentos. Vou testar quando chegar no trabalho ou em casa. Estou em serviço externo hoje.

[DaltonSilva]

Bom dia.
O ideal seria você colocar o resultado esperado em algumas linhas para testar possíveis soluções.
Mas resolvi arriscar com esta fórmula, referente à linha 3.
Cole em qualquer coluna vazia e copie para baixo.

Código: Selecionar todos

=SE(OU(E(TEMPO(MOD(A3;1);0;0)<=8/24;B3-A3>=12/24);E(MOD(A3;1)>8/24;B3-A3>=12/24));(B3-A3)/3;0)

Good luck!

Amigo. Deixa eu tentar explicar mais detalhadamente o resultado que estou esperando. Parando para analisar melhor aqui é mais complexo do que eu imaginava (ao final eu explico o porquê).
Veja a figura abaixo:

- Os serviços normais são de 24h - horário padrão de 8h de um dia às 8h do dia subsequente.
- O período de sobreaviso, comumente, é das 8h às 20h. Entretanto, este período não está registrado. A forma de chegar a conclusão de que o funcionário estava de sobreaviso neste "vácuo" é identificar que no dia anterior o funcionário estava de serviço por 24h, ou através da identificação de que este funcionário assumiu o serviço no ponto às 20h (neste caso é meio complicado pois o funcionário pode quebrar o sobreaviso por necessidade do serviço, então não necessariamente o funcionário assumiria às 20h). Ou, ainda, identificando que na coluna "Observação" há a palavra sobreaviso, mesmo que seja num período inferior ao pré-estabelecido (ou seja, de 12h de sobreaviso).

Nesta imagem abaixo, temos a outra forma de configuração do sobreaviso:

Nesta hipótese, o sobreaviso é das 20h de um dia às 8h do dia subsequente.
- O resultado que quero obter é quantas horas serão contabilizadas de sobreaviso pela fórmula. Ou seja, aplicar a proporção de 1h computada de hora trabalhada para cada 3h de sobreaviso. Entretanto, antes de efetuar o cálculo, o excel tem que reconhecer em que situações irá aplicar o cálculo, entendeu?

É justamente por esses motivos que falei no início do post que é complexo. Fazer o excel reconhecer quando efetuar o cálculo.
Pensei em padronizar a escrita da palavra sobreaviso na coluna "observação", tendo em vista escritas diversas por funcionários diversos (uns escrevem "sobre aviso", outros "sobreaviso"), e tentar fazer com quê o excel só faça o cálculo quando a célula "tal" encontrar esta palavra. Mas, sinceramente, não tenho ideia de como fazer.
Daí, irmão, se for possível me ajudar nessa. Mas, entendo perfeitamente a complexidade da situação.

[Estevaoba]

O importante é verificar se existe um padrão.
Se você não puder estabelecer esse padrão ou padrões, coloque por favor manualmente os resultados esperados em algumas linhas, explicando por que devem ser assim ou assado.
Não só os de sobreaviso.
Por exemplo, na primeira foto do seu último post, as linhas 3, 5 e 7 estão marcadas como serviço normal.
Mas eu acho que a linha 4 também é normal, já que não é uma data consecutiva ao dia anterior.
Se a palavra sobreaviso ou sobre aviso aparecer toda vez, com certeza fica mais fácil.

Tente colocar os resultados esperados para todas as situações possíveis e tentaremos reproduzi-los com fórmulas ou outra solução.

Ab.

[DaltonSilva]

O importante é verificar se existe um padrão.
Se você não puder estabelecer esse padrão ou padrões, coloque por favor manualmente os resultados esperados em algumas linhas, explicando por que devem ser assim ou assado.
Não só os de sobreaviso.
Por exemplo, na primeira foto do seu último post, as linhas 3, 5 e 7 estão marcadas como serviço normal.
Mas eu acho que a linha 4 também é normal, já que não é uma data consecutiva ao dia anterior.
Se a palavra sobreaviso ou sobre aviso aparecer toda vez, com certeza fica mais fácil.

Tente colocar os resultados esperados para todas as situações possíveis e tentaremos reproduzi-los com fórmulas ou outra solução.

Ab.

Opa, colega.
Aquelas marcações de serviço normal foi só para ilustrar o padrão de serviço normal, que no caso em tela seria de 24h.
Raciocinando melhor aqui, por existir serviços (capacitação, por exemplo) que duram menos do que 12h, seria interessante estabelecer como padrão a referência às palavras da coluna "Observação" (sobreaviso ou sobre aviso - daria para padronizar).
Então, o resultado seria da seguinte forma, exemplos:
- entrada: 01/07/2018 08:00 ; saída: 01/07/2018 20:00 (sobreaviso das 20h do dia 1 às 8h do dia 2) --> Diferença (12h) --> resultado 4h em banco de horas, ou seja 12h/3.
- entrada: 01/07/2018 20:00 / saída 02/07/2020 08:00 (sobreaviso das 8h às 20h do dia 1) --> Diferença (12h) --> resultado 4h em banco de horas, ou seja, 12h/3
Obs.: Se esse sobreaviso durar menos tempo, por exemplo, iniciar às 8h do dia 1 e cessar às 17h do mesmo dia, o resultado seria a diferença (9h) / 3 = 3h em banco de horas.
Para facilitar a construção de uma solução acredito ser mais viável buscar a informação do sobreaviso na coluna "Observação".
Espero que eu tenho conseguido me fazer entender.
Seria, basicamente, isso. Embora, na atualidade, nem todos os funcionários registrem o sobreaviso no campo observações do sistema, aos poucos dá para eu ir alterando, caso a caso, e daqui para frente, determinar essa padronização para registro no sistema por parte dos funcionários.

[Estevaoba]

Realmente fica difícil estabelecer uma padrão para automatizar o cálculo.
Seria interessante preencher manualmente os horários de início e término do sobreaviso, nas colunas H e I, por exemplo, quando os horários forem diferentes das colunas A e B. Eu até criei fórmulas para extrair esses valores da coluna G, mas não sei se cobre todas as situações.
Isso facilitaria o cálculo na coluna J buscando por palavra chave na coluna G.

Desculpe por não ser uma solução definitiva, mas espero que pelo menos te dê uma luz pra você alcançar seu objetivo.

Ab.

[DaltonSilva]

Realmente fica difícil estabelecer uma padrão para automatizar o cálculo.
Seria interessante preencher manualmente os horários de início e término do sobreaviso, nas colunas H e I, por exemplo, quando os horários forem diferentes das colunas A e B. Eu até criei fórmulas para extrair esses valores da coluna G, mas não sei se cobre todas as situações.
Isso facilitaria o cálculo na coluna J buscando por palavra chave na coluna G.

Desculpe por não ser uma solução definitiva, mas espero que pelo menos te dê uma luz pra você alcançar seu objetivo.

Ab.

Fala, meu amigo.
Visualizei aqui a solução proposta por você. Realmente, foi excepcional. Agradeço demais.
Percebi que alguns cálculos do sobreaviso ficaram "zerados". Porém, esse fato ocorreu tendo em vista o registro que o funcionário registrou de um dia para o outro de que estava "quebrando" o sobreaviso.
Nestes casos, tendo em vista que a construção da sua fórmula ter encaixado bem onde eu estava querendo chegar, basta eu alterar no sistema a forma que o funcionário registrou esse retorno ao serviço normal (quebrando o sobreaviso). E daqui para frente padronizar esse tipo de registro para que a fórmula se amolde ao resultado finalístico.
Valeu mesmo, meu irmão.
Forte abraço.