Concorra ao sorteio do livro Decisões com B.I. escrito pelo nosso colunista Fábio Primak. Clique aqui para participar.
Aprenda agora como criar o famoso Banco de Horas no Microsoft Excel 2003. Veja como calcular o número de horas trabalhadas e quanto receber por elas.
Grau de Dificuldade: 7
Olá turma!
Hoje, vamos dar continuidade ao desenvolvimento do Banco de Horas no Microsoft Office Excel 2003.
Caso você não tenha lido ou acompanhado a primeira parte desse artigo, clique aqui. Se você deseja baixar a planilha pronta, clique aqui.
1) Selecione o intervalo de células I2 até L2 (I2:L2) e clique no botão mesclar. Escreva nessa célula o rótulo “Total Geral”. Em seguida, digite os valores CUMPRIU JORNADA? na célula I3, HORAS TRABALHADAS na célula J3, MINUTOS TRABALHADOS na célula K3 e SEGUNDOS TRABALHADOS na célula L3, conforme a imagem abaixo.
Observação: Lembre-se que para obtermos duas linhas dentro da mesma célula basta utilizar o atalho ALT + ENTER.
2) Clique na célula I4 e insira a função SE para verificar se o funcionário cumpriu ou não a carga horária da seguinte forma; =SE(G4>=B4;"SIM";"NÃO").
Explicando a fórmula:
=Se(Jornada Cumprida >=Jornada Diária; “SIM”; “NÃO”)
Se a jornada cumprida pelo funcionário for maior ou igual ao número de horas que ele deve cumprir diariamente, diremos que ele cumpriu, caso contrário, diremos que ele não cumpriu. Você pode utilizar a alça de redimensionamento para calcular as próximas linhas, conforme na imagem abaixo:
3) Em seguida, clique na célula J4 e insira a fórmula necessária para que apareça o total de horas trabalhadas, que corresponde a =HORA JORNADA + HORA EXTRA, isto é, digite a fórmula =G4+H4 para obter o resultado, conforme na imagem abaixo (não se esqueça de formatar essa coluna com o formato hora em Formatar / Células):
4) Agora, clique na célula K4 para calcular o total de minutos trabalhados em um determinado dia com a fórmula =J4*1440. Note que estamos multiplicando o numero de horas trabalhadas por 1440 que, por sua vez, corresponde a 60 minutos em 24 horas:
1 hora – 60 minutos.
24 horas – 1440 minutos.
Observação: não se esqueça de selecionar o resultado da soma e formatá-lo como GERAL em FORMATAR / Células. Se preferir, utilize o atalho CTRL + ~ para transformar o formato de hora em decimal.
5) Logo após, clique na célula L4 para calcular o total de segundos trabalhados em um determinado dia com a fórmula =J4*86400. Note que estamos multiplicando o numero de horas trabalhadas por 86400 que, por sua vez, corresponde a 60 segundos em 24 horas:
1 minuto – 60 segundos
1 hora – 3600 segundos.
24 horas – 84600 segundos.
Observação: não se esqueça de selecionar o resultado da soma e formatá-lo como GERAL em FORMATAR / Células. Se preferir, utilize o atalho CTRL + ~ para transformar o formato de hora em decimal.
6) Vamos tirar a prova real dos nossos resultados, a fim de que possamos ampliar nosso conhecimento sobre o formato de data e hora. É preciso que você selecione o intervalo de células M2 até P2 (M2:P2) e clique no botão mesclar. Escreva nessa célula o rótulo “Prova Real”. Em seguida, digite os valores Prova Real - Minutos? na célula M3, Prova Real – Minutos na célula N3, PROVA REAL - SEGUNDOS na célula O3 e CONVERTENDO HORA PARA DECIMAL na célula P3, conforme a imagem abaixo:
Observação: Lembre-se que para obtermos duas linhas dentro da mesma célula basta utilizar o atalho ALT + ENTER.
7) Clique na célula M4 e insira a fórmula =L4/60, conforme explicado na imagem abaixo:
Para obter minutos:
8) Clique na célula N4 e insira a fórmula M4/1440, conforme explicado na imagem abaixo:
Para obter horas:
9) Clique na célula O4 e insira a fórmula =M4*60, conforme explicado na imagem abaixo:
Para obter segundos:
10) Agora vem o momento mais interessante. Vamos converter o total de horas trabalhadas para o formato decimal com o auxílio de duas funções pré-definidas do Microsof Excel. São elas:
FUNCAO HORA
Serve para extrairmos (retirarmos) apenas a parte referente às hora de uma determinada hora completa. Exemplo: 14:00 seria igual a 14.
FUNCAO MINUTO
Serve para extrairmos (retirarmos) apenas a parte referente aos minutos de uma determinada hora completa. Exemplo: 14:35 seria igual a 35.
Clique na célula P4 e digite a fórmula =HORA(J4)+(MINUTO(J4)/60) para obter o total de horas trabalhadas no FORMATO DECIMAL.
Atenção - Importante:
A necessidade de convertermos o total de horas trabalhadas é por causa da incompatibilidade existente ao SOMAR horas no Microsoft Excel, conforme você pode perceber na imagem abaixo:
Note que a soma das horas na coluna azul resultou um resultado errado, fato este que não ocorreu ao somarmos os valores previamente convertidos para decimal outra coluna.
11) Em seguida, clique na célula Q2 e digite o rótulo RESULTADO, bem como digite o rótulo TOTAL A RECEBER na célula Q3, conforme a imagem abaixo:
12) Agora, clique na célula A14 e crie a tabelinha abaixo, conforme na imagem:
13) Digite o valor desejado a ser pago por cada hora trabalhada na célula B15, conforme a imagem abaixo:
15) Para obter automaticamente o TOTAL A RECEBER, basta que você insira a fórmula =(M4/60)*$B$15 na célula Q4.
Explicando a fórmula: Ou seja, basta que você obtenha o total de horas trabalhadas e multiplique cada hora pelo seu valor, conforme a imagem abaixo:
16) Agora, clique na célula B16 e insira a fórmula =SOMA(K4:K12)/60 para obter o total de horas trabalhadas no formato decimal, conforme a imagem abaixo:
17) Clique na célula B17 e insira a fórmula =SOMA(K4:K12) para obter o total de minutos trabalhados, conforme a imagem abaixo:
Não se esqueça de utilizar o botão SEPARADOR DE MILHARES para formatar o resultado:
18) Clique na célula B18 e insira a fórmula =SOMA(L4:L12) para obter o total de segundos trabalhados, conforme a imagem abaixo:
Do mesmo modo, não se esqueça de utilizar o botão SEPARADOR DE MILHARES para formatar o resultado:
19) Por fim, clique na célula B19 e insira a fórmula =SOMA(Q4:Q12) para obter, de acordo com número de horas trabalhadas, o SALÁRIO DO FUNCIONÁRIO, conforme a imagem abaixo. Formate o resultado dessa fórmula como MOEDA ou CONTÁBIL.
Pronto, espero que tenham gostado de criar um Banco de Horas no Microsoft Office Excel 2003, bem como que vocês tenham compreendido o funcionamento desse formato no programa. Caso tenham alguma dúvida, enviem um e-mail para ronaldosdb@weblivre.net
Um forte abraço,
Ronaldo dos Santos Silva
Autor: Ronaldo dos Santos Silva
Compartilhe:
Bruno Gardelin em 30/06/2008
[ gostei da planilha acima, mas como fazer quando for horas negativas?]2 Você tem alguma solução para quando houver "adicionais noturnos" ?
agradeço
Célia em 30/06/2008
Amei a planilha era tudo que eu precisava aprender, mas como fazer quando for horas negativas?
Jorge Martellet em 19/01/2008
Parabéns, muito bom!
Apenas como sugestão concordo com o que diz Paulo Marinho.
Abraço
Paulo Marinho em 09/10/2007
gostei da planilha acima, mas como fazer quando for horas negativas?
Roberto Sachse em 01/10/2007
gostei muito, espero por novidades
Evandro em 01/10/2007
Muito bom!! Adoraria receber o mesmo por e-mail, se for possivel! desde ja agradeço!
EMERSON em 01/10/2007
OTIMO
FAGNER em 24/09/2007
MUITO OTIMA A DEMOSTRAÇÃO, APRENDI BASTANTE!!!
2004-2008 Weblivre.net Todos os direitos reservados.
Quem somos | Política de Acesso | Anuncie | Na Mídia | Seja um Colunista | Sugestão de Pauta | Fale Conosco
Idealização: Webla - Comunicação Digital