Guru do Excel e Power BI

Boa tarde!

Tenho a formula a baixo:

=SE(F2="";"";SE(E(F2>="07:55:00"+0;F2<="08:05:00"+0);"08:00";F2))

Ela faz a seguinte leitura:

Se no F2 estiver em branco deixa branco se não e se F2 for maior que "07:55:00" e menor 08:05:00 então considera 08:00

Porem esta formula vale para segunda a sexta no sabado a regra muda para 11:55 a 12:05 gostaaria de acrescentar esta regra.

agluem pode me ajudar?

CONSIDERANDO QUE A COLULA A INFORMA O DIA DA SEMANA.

Olá,

Supondo que na célula F2 tenha data e hora, as fórmulas seguintes fazer o pedido. Escolha a que melhor te atende.

A primeira compara a hora com 07:55 e 8:05: (fiz indentação e quebra de linhas só pra tormar mais fácil entender)
A segunda compara a hora com um hora base, mais ou menos uma tolerância:
Jimmy San Juan

Jimmy San Juan

Peço desculpa eu coloque a formula errada que gostaria de ajustar.

Na verdade não seria na entrada do funcionario e sim na saida.

=SE(H2="";"";SE(E(H2>="16:55:00"+0;H2<="17:05:00"+0);"17:00";H2))

O que eu quero acresentar e se for sabado a formula será a mesma porem com mudança de horario =SE(H2="";"";SE(E(H2>="11:55:00"+0;H2<="12:05:00"+0);"17:00";H2)) pois os funcionarios so trabalham no sabado ate 12:00

Vale lembra que a coluna A ja informa o dia da semana.

Olá,

Procure entender a fórmula para que tenha versatilidade de usá-la em outras condições.

Fiz em várias linhas apenas para deixar claro suas partes e finalidades. Funciona igual às que tem uma linha só: é so copiar e colocar na célula.

Troque F2 pelo endereço real onde está a data/hora. Se a data estiver em célula diferente da hora, então você tem que adaptar, ou dizer onde esta cada coisa. Para adaptar troque o F2 da segunda linha pelo endereço da célula que contém a data. Os demais F2 se referem à hora, e devem ser também alterados se a hora estiver noutro canto.

=SE(F2="";"";
            SE(DIA.DA.SEMANA(F2;2)<=5;
                        SE( E(MOD(F2;1)>=VALOR.TEMPO("07:55:00");MOD(F2;1)<=VALOR.TEMPO("08:05:00")); INT(F2)+VALOR.TEMPO("08:00:00"); F2);
                        SE( E(MOD(F2;1)>=VALOR.TEMPO("11:55:00");MOD(F2;1)<=VALOR.TEMPO("12:05:00")); INT(F2)+VALOR.TEMPO("12:00:00"); F2)
                 )
          )

A primeira linha verifica se há algo digitado. Aqui pode-se incluir uma consistência (verificação) se o digitado é realmente uma data/hora, e usar o operador lógico "E" para consistir tanto data quanto hora, se forem separados.

A segunda linha verifica se é dia de semana ou final de semana (resultados entre 6-sábado e 7-domingo). Se for dia de semana (resultados entre 1-segunda e 5 sexta) executa a terceira linha. Se for final de semana executa a quarta, que tem horários distintos da terceira.

Na terceira linha (vale para a quarta também) você tem os campos em azul que são a hora de saída a partir da qual deve ser arredondada para cima, a hora de saída até a qual deve ser arredondada para baixo, e a hora arredondada.

A segunda fórmula que passei tem a mesma estrutura, porém em vez de considerar as hora de saída, considera a hora base (hora do final do turno), a tolerância de 5 minutos (para cima ou para baixo), e a hora arredondada caso esteja dentro da tolerância.

=SE(F2="";"";
            SE(DIA.DA.SEMANA(F2;2)<=5;
                        SE( ABS(MOD(F2;1)-VALOR.TEMPO("08:00:00"))<=VALOR.TEMPO("0:00:05"); INT(F2)+VALOR.TEMPO("08:00:00"); F2);
                        SE( ABS(MOD(F2;1)-VALOR.TEMPO("12:00:00"))<=VALOR.TEMPO("0:00:05"); INT(F2)+VALOR.TEMPO("12:00:00"); F2)
                 )
         )

Ambas fazem a mesma coisa mas de formas diferentes. Escolha se prefere trabalhar com horário de baixo e de cima, ou com horário base e tolerância. Copie a form escolhida, cole na planilha, altere os endereços F2 para o endereço real que contém data/hora, e altere os horários (em azul nas fórmulas acima).

Se tiver qualquer dúvida, é só perguntar.

Jimmy San Juan

Jimmy San Juan

Primeiramente gostaria de lhe agradece e dizer que a formula funcionou muito bem.
Sempre faço isso pois e importante não só copiar como entender o que esta fazendo.
Fiz isso sim pois e estou mandando um exemplo da planilha para voce entender melhor. o fato e que minha coluna A informa o dia da semana a C informa a data e a formula para marcação do ponto com a tolerância para dia de semana e fim de semana é na coluna I fazendo a leitura da informação importada no sistema do coluna H.

Qualquer sugestão ou melhoria na nas formula ou planilha fico grato.


Mas de qualquer forma lhe agradeço de mais.

Poucas observações:

• A coluna A é o dia da semana da data da coluna C. As fórmulas das colunas K e M usar o dia da semana, logo se referenciam à A. Eu mudaria isso fazendo com que essas fórmulas se referenciem à coluna C, onde está a data. Se fizer dessa forma, não terá problemas caso, por exemplo, mude a fórmula da coluna A para =TEXTO(C2;"ddd"), passando a usar o dia da semana no formato curto (Seg em vez de Segunda-feira).
    
• A fórmula da coluna A pode ser alterada de =TEXTO(C2;"dddd") para =C2 se você formatar a exibição da coluna A com DDDD.
   
• A coluna A pode até ser extinta com a unificação das colunas A e D. Basta apagar a coluna A e mudar a formatação das células da C de dd/mm/aaaa para dddd dd/mm/aaaa. Nenhuma fórmula dependente da C seria afetada.
   
• As fórmulas das colunas K e M não consideram a possibilidade do DOMINGO. Não sei se é proposital ou não, mas parece não ser. Caso não seja, a fórmula seia: =SE(dia.da.semana(C2;2) >=6 ;"00:00:00";"01:00:00"). Se é pra ser apenas sabado mesmo, então a fórmula fica =SE(dia.da.semana(C2;2) =6 ;"00:00:00";"01:00:00"). Na função dia.da.semana, configurada com parâmetro 2, o retorno 6 significa sábado, e 7 domingo.
   
• A fórmula da coluna N não prevê período trabalhado inferior a período esperado. Quando o período trabalhado é maior que o esperado, ok. Quando não é, não calcula.

Se esta mensagem te ajudou, deixe seu LIKE clicando no ícone OBRIGADO.
Se o problema está resolvido, clique em MARCAR RESOLVIDO

Jimmy San Juan