Este fórum está sendo desativado

[DaltonSilva]

Mestres, bom dia!
Estou passando por alguns probleminhas em chegar ao resultado esperado a fim de consolidar um "sistema" de banco de horas para o setor em que trabalho.
1º Problema:
- Na consulta fSobreaviso, tenho 5 (cinco) colunas - Servidor, Data, Observação, Início Sobreaviso e Fim Sobreaviso.
- As colunas Início Sobreaviso e Fim Sobreaviso foram criadas a partir das informações entre parênteses da coluna Observação. Porém, os valores inseridos nestas colunas partem da função "Texto Inserido Entre os Delimitadores" como etapa. Com isso, mesmo convertendo esses valores para hora, não consigo (dá erro da fórmula) criar uma coluna personalizada com o seguinte roteiro: SE o valor da coluna Início do Sobreaviso for maior que o valor da coluna Fim do Sobreaviso, então (24:00 - Início Sobreaviso) + Fim Sobreaviso, caso contrário, Fim Sobreaviso - Início do Sobreaviso. Por exemplo: se o sobreaviso iniciou às 20:00 do dia 20/11/2021 e terminou às 08:00 do dia 21/11/2021, o resultado esperado seria 12:00 para que eu pegue esse valor e divida por 3, obtendo o total de 4h contabilizadas como horas trabalhadas. Ou seja, para cada 3h em sobreaviso é contabilizada 1h como hora trabalhada. Outro exemplo do resultado seria se o sobreaviso se iniciasse às 08:00 e fosse até às 20:00, bastaria subtrair 20 - 8 = 12, também contabilizando-se 3h como hora trabalhada.
2º Problema:
- Preciso calcular a carga horária mensal a ser trabalhada por cada servidor, por mês. O cálculo é feito da seguinte forma: para cada dia útil, contabiliza-se 8h. Com isso, num mês com 23 dias úteis, o servidor dele trabalhar 184h;
- Porém, tenho a questão dos afastamentos a considerar. Nos afastamentos do tipo férias e do tipo deslocamento para nova sede, é necessário considerar esses dias como não útil. Exemplo: caso o servidor esteja de férias de 1 a 10 no mês de dezembro de 2021, o quantitativo total a ser trabalhado seria de 120h (15 dias úteis x 8h). Ou seja, o cálculo dos dias úteis foi considerado do dia 11 ao dia 31 de dezembro de 2021. A mesma coisa ocorreria com o afastamento de deslocamento que englobasse o período de 25 de setembro de 2021 a 24 de outubro de 2021. Neste caso, as horas a trabalhar no mês de setembro seria de 136h (17 x 8) e no mês de outubro de 40h (5 x 8).
- Outra coisa, tenho duas tabelas no modelo de dados, quais sejam: fFeriados_Nacional e fFeriados_Local. No caso em tela, tenho que considerar os feriados, logicamente, para chegar ao quantitativo de dias úteis de determinada faixa entre datas.

3º Problema:
- Com relação às LTS (Licença para Tratamento da Própria Saúde), tenho duas regras a serem aplicadas em cada caso. A primeira regra é a seguinte: se o servidor iniciar o mês já te LTS, tenho que pegar esse período e considerar dia não útil. Se o servidor iniciar a LTS no decorrer do mês, pego esse período e verifico quantos serviços ele estava escalado. A cada serviço escalado, verifico quantas horas estavam previstas para esses serviços (se de 12h, de 8h ou 24h). Para cada dia de escalação do servidor, contabiliza-se, justamente, as horas para as quais ele estava escalado antes de apresentar o atestado.
Exemplo 1: No mês de agosto de 2021, determinado servidor entrou com LTS de 15 a 19 estando de serviço 24h e no dia de início e fim do atestado. Contabiliza-se 48h como se trabalhado tivesse.
Exemplo 2: no mês de agosto de 2021, determinado servidor entrou com LTS de 25 de agosto a 4 de setembro. Do dia 25 a 31 de agosto o servidor havia previsão de tirar dois serviços de 24h, 25 e 29. Contabiliza-se 48h tal qual o primeiro exemplo, e, complementarmente, de 1 a 4 de setembro, considera dia não útil.

Inicialmente, eu havia pensado em fazer uma tabela "personalizada" para cada servidor, "juntando" as tabelas fFeriados_Nacional, fFeriados_Local e fAfastamentos_DiasUteis_PDI (Lista) a fim de calcular a quantidade de dias úteis entre duas datas, contudo, achei que iria ficar muito trabalhoso e peço a ajuda de vocês para chegar a uma solução.
Ao final, preciso que eu tenha adicionada à consulta fHoras_TrabalhadasTotal_PDI às colunas: "Horas Contabilizadas Sobreaviso" (que é o resultado do período em que o servidor esteve de sobreaviso dividido por 3), "Qtde Carga Horária Mensal (h)" (que seria o resultado dos dias úteis a trabalhar x 8h), "Saldo Anos Anteriores" (referente à coluna Saldo anterior da Consulta fSaldosAnt2021), "Abono nas Horas" (que seria a quantidade de horas lançadas manualmente por uma pessoa responsável que representasse as horas que estavam previstas dentro do período da LTS que não foram trabalhadas, porém, que deverão ser contabilizadas), e "Ajuste no Saldo" (seria uma coluna auxiliar criada manualmente caso fosse necessário algum ajuste não previsto nas regras elencadas acima).

Seguem em anexo as pastas dos arquivos que fazem parte da base de dados, bem como o arquivo do Excel (pasta de trabalho) com as tabelas já inseridas e trabalhadas (ETL) dentro do Power Query.
Espero que eu tenha conseguido vocês entenderem. Estou à disposição para dúvidas.
Desde já agradeço.

[DaltonSilva]

Mestres, bom dia. Salientando que ainda continuo sem encontrar uma solução na compilação do banco de horas. Grato!

[DJunqueira]

Sua tabela de saldo de horas está com uma salada de formatos, melhor corrigir, preferencialmente p/ números e não texto.

[DaltonSilva]

Meu amigo Djunqueira. A questão é a seguinte. O Excel não aceita (pelo menos foi a conclusão a que cheguei em uma pesquisa na internet) a inserção manual de horas negativas. Se o saldo negativo fosse resultado de uma operação, tudo bem, o Excel, no formato configurado, aceitaria. Quando assumi a função atual, recebi esses saldos em papel, que eram calculados sem auxílio de planilha. Se tiver alguma sugestão de como resolver isso, agradeço. Uma solução nessa direção seria mais um passo para chegar ao resultado final.

[DJunqueira]

Horas são representadas por números decimais, números decimais podem ser negativos, a simples visualização no formato de horas pode ser configurada na formatação das células.

[DaltonSilva]

Amigo DJunqueira, bom dia. Grato pela ajuda, desde já.
Veja, por gentileza, se o novo arquivo Banco_de_Horas_Garanhuns.xlsx, em anexo, está de acordo com o que você citou no último post.
Desculpa se não consegui compreender o que estava querendo repassar a fim de corrigir a salada da consulta fSaldos_Ant_2021.
No mais, coloco-me novamente à disposição para tentarmos construir esse banco de horas "atípico" utilizando o Power Query.

[DJunqueira]

Hehe, acabei de responder s/ um tópico de hora negativa.
viewtopic.php?f=9&t=16271&p=68105#p68105

[DaltonSilva]

Meu amigo, agradeço a disposição em ajudar. Desculpa a demora em responder. Tive que viajar e ainda estou em viagem. Assim que der, brevemente, darei um feedback sobre a sua postagem nesse outro post.

[DaltonSilva]

Bem pessoal, primeiramente Feliz Natal a todos.
Em segundo lugar, permaneço com o itento de fazer essa consolidação do banco de horas ta qual descrito na primeira mensagem.
Erros/dúvidas de formato superadas, porém, ainda persiste a problemática em encontrar uma forma de chegar ao resultado esperado.
Desde já agradeço.

[DJunqueira]

Feliz Natal!
Em q pé estamos afinal?

[DaltonSilva]

Fala, meu amido DJunqueira!
Soluções pendentes remanescente:
1º Problema:
- Preciso calcular a carga horária mensal a ser trabalhada por cada servidor, por mês. O cálculo é feito da seguinte forma: para cada dia útil, contabiliza-se 8h. Com isso, num mês com 23 dias úteis, o servidor deve trabalhar 184h;
- Porém, tenho a questão dos afastamentos a considerar. Nos afastamentos do tipo (Férias - EST, Teletrabalho Integral, Trabalho Remoto COVID-19, Decisão Judicial – EST, Deslocam. Nova Sede Situação em Transito – EST e Afas. Servir a Outro Órgão (REQUISIÇÃO) – EST) é necessário considerar esses dias como não útil. Exemplo: caso o servidor esteja de férias de 1 a 10 no mês de dezembro de 2021, o quantitativo total a ser trabalhado seria de 120h (15 dias úteis x 8h). Ou seja, o cálculo dos dias úteis foi considerado do dia 11 ao dia 31 de dezembro de 2021. A mesma coisa ocorreria com o afastamento de deslocamento que englobasse o período de 25 de setembro de 2021 a 24 de outubro de 2021. Neste caso, as horas a trabalhar no mês de setembro seriam de 136h (17 x 8) e no mês de outubro de 40h (5 x 8).
- Outra coisa, tenho uma tabela no modelo de dados denominada fFeriados_Consolidado. No caso em tela, tenho que considerar os feriados, logicamente, para chegar ao quantitativo de dias úteis de determinada faixa entre datas.

2º Problema:
- Com relação às Licenças, que também são afastamentos, quais sejam: Lic. Tratamento de Saúde – EST e Falecimento Pessoa da Familia - EST), tenho duas regras a serem aplicadas a depender do caso. A primeira regra é a seguinte: se o servidor iniciar o mês já te LTS, tenho que pegar esse período e considerar dia não útil. Se o servidor iniciar a LTS no decorrer do mês, pego esse período e verifico quantos serviços ele estava escalado. A cada serviço escalado, verifico quantas horas estavam previstas para esses serviços (se de 12h, de 8h ou 24h). Para cada dia de escalação do servidor, contabiliza-se, justamente, as horas para as quais ele estava escalado antes de apresentar o documento comprobatório.
Exemplo 1: No mês de agosto de 2021, determinado servidor entrou com a licença de 15 a 19, estando de serviço 24h no dia de início e no dia do fim do atestado. Contabilizam-se 48h como se trabalhado tivesse.
Exemplo 2: no mês de agosto de 2021, determinado servidor entrou com Licença de 25 de agosto a 4 de setembro. De 25 a 31 de agosto o servidor havia previsão de tirar dois serviços de 24h, 25 e 29. Contabilizam-se 48h tal qual o primeiro exemplo, e, complementarmente, de 1 a 4 de setembro, considera dia não útil.
Exemplo 3: no mês de agosto, determinado servidor entrou com Licença de 20 de agosto a 10 de dezembro. De 20 a 31 de agosto aplica-se a regra do abono (como é chamada) – conforme explanação do Exemplo 2 e, complementarmente, de 1 de setembro a 10 de dezembro, considera-se dia não útil. Contudo, preciso restringir esses períodos, por mês. Ou seja, de 1 a 30 de setembro, de 1 a 31 de outubro, de 1 a 30 de novembro e de 1 a 10 de dezembro. A questão que gira em torno desse desmembramento em meses é que o banco de horas é mensal. A cada mês é feito o cálculo do saldo. Tenho que divulgar o saldo do mês anterior até o 5º dia útil do mês subsequente demonstrando o impacto (o saldo final) para o mês referenciado, mantendo a visualização dos meses anteriores para fins de controle e transparência do processo.

Ao final, preciso que eu tenha adicionada à consulta fHoras_TrabalhadasTotal_PDI as colunas: "Contabilização do Sobreaviso" (que é o resultado do período em que o servidor esteve de sobreaviso dividido por 3), "Qtde Carga Horária Mensal (h)" (que seria o resultado dos dias úteis a trabalhar x 8h), "Saldo Anos Anteriores" (referente à coluna Saldo anterior da Consulta fSaldos_Ant), "Abono nas Horas" (que seria a quantidade de horas lançadas manualmente por uma pessoa responsável que representasse as horas que estavam previstas dentro do período das Licenças da regra do abono e que não foram trabalhadas, porém, deverão ser contabilizadas), e "Ajuste no Saldo" (seria uma coluna auxiliar criada manualmente caso fosse necessário algum ajuste não previsto nas regras elencadas acima).
Segue abaixo, a formatação final desejada:


A coluna "Total contabilizado" seria a soma dos dados das "colunas Total Trabalhado", "Contabilização do Sobreaviso" e "Abono nas Horas".
O Saldo Mensal seria o resultado da expressão: ("Total Contabilizado"+"Ajuste no Saldo")-"Qtde Carga Horária Mensal (h)"

Seguem em anexo as pastas dos arquivos que fazem parte da base de dados, bem como o arquivo do Excel (pasta de trabalho) com as tabelas já inseridas e trabalhadas (ETL) dentro do Power Query.
Espero que eu tenha conseguido fazer com que tenha entendido. Estou à disposição para dúvidas.
Desde já agradeço.

[DaltonSilva]

DJunqueira, meu amigo.
Em princípio, levando em consideração as implementações que foi fazendo na base de dados com o Power Query, creio que eu esteja, praticamente, a ponto de resolver a consolidação desse banco de horas.
A dificuldade que estou tendo é a seguinte (desde já peço a sua ajudar para solucionar):

Com essa amostra da tabela fAfastamentos_Dias_Uteis preciso chegar aos seguintes resultados:
- Que cada intervalo entre a data inicial e a data final do afastamento fique dentro do mesmo mês. Exemplo, no afastamento que se inicia em 20/10/2021 e finda em 19/10/2022, que o Power Query, em cada linha faça a seguinte decomposição: 20/10/2021 a 31/10/2021, 01/11/2021 a 30/11/2021, 01/12/2021 a 31/12/2021, 01/01/2022 a 31/01/2022, e assim sucessivamente, até 01/10/2022 a 19/10/2022.
- A intenção é repetir esse processo para todos os afastamentos que encontram-se lançados na tabela. A exceção, logicamente, seria com relação aos afastamentos que se iniciassem e findassem no mesmo mês.
Segue em anexo a base de dados resumida para tentar me auxiliar no processo dessa solução.
Desde já agradeço.