Manipulando datas e horas no Excel

Este artigo tem o objetivo de mostrar ao leitor um pouco sobre a manipulação de datas e horas no Excel.

por Robert Martim

Este artigo tem o objetivo de mostrar ao leitor um pouco sobre a manipulação de datas e horas no Excel.

O artigo é retirado do MÓDULO 3 DE TREINAMENTO NO EXCEL - FÓRMULAS NO EXCEL: MANIPULANDO DATAS E HORAS o qual pode ser adquirido, por apenas R$10, no seguinte endereço http://shop.linhadecodigo.com.br/treinamento.asp?id=307 (VIA DOWNLOAD) Ao adquirir este curso o leitor ainda ganha 30 pontos no programa TopDev da Microsoft para compras via download e 60 pontos para compras via CD. Não perca esta oportunidade!

Este artigo assume um conhecimento prévio de como datas e horas são manipuladas no Excel. Se você ainda tem alguma dificuldade nesta área este módulo de treinamento certamente é o que você precisa para compreender esta área ainda obscura do Excel. Além de cobrir todos os tipos de manipulações com datas e horas na planilha o leitor ainda aprenderá a usar o VBA para criar funções que não existem no Excel, mas são muito requisitas pelos mais variados níveis de usuários.

Uma dúvida que sempre surge nos usuários de Excel é como subtrair, por exemplo, 176:00:00 de 150:00:00. Aqui temos uma situação interessante. Como é explicado no Treinamento, não existe 176 horas ou 150 horas para o Excel. O que existe são 176/24 e 150/24 (7,33 dias e 6,25 dias respectivamente). Novamente, o que ocorre é que podemos formatar 7,33 para mostrar 176:00:00 ou até mesmo digitar o valor 176 (o qual representaria 176 dias e não 176 horas).

Para mostrar o número total de horas, basta utilizar o seguinte formato: [h]:mm:ss. Os colchetes instruem o Excel a mostrar o somatório das horas ao invés da fração do primeiro dia fracionado.

Ao tentar subtrair 176 horas de 150 horas o valor retornado é negativo. No sistema de datas 1900 isso não é válido. Ao modificar para o sistema 1904 é possível subtrair uma hora maior de uma menor. Neste caso específico ao efetuarmos a subtração 150–176 obtemos –26 horas. Se os valores se encontram neste formato a outra opção e "envelopar" a subtração na função ABS: =ABS(150:00:00-176:00:00)

Novamente, embora a formatação mostre claramente as 26 horas, este não é o valor efetivamente utilizado pelo Excel para efetuar os cálculos. Observe, contudo, que você poderia ter simplesmente feito a seguinte conta: =176:00:00-150:00:00, para obter 26 horas sem a necessidade de complicar o problema.

A função ABS funciona bem neste exemplo porque não estamos preocupados com a direção das horas. Veja que ao declarar 176 horas estamos dizendo ao Excel que este valor equivale a 7,33 dias.

Imagine uma pessoa que trabalha no turno da noite em um supermercado. Se esta pessoa inicia o trabalho às 21 horas e sai às 5 horas da manhã do dia seguinte, a direção da contagem das horas começa a fazer uma diferença. Como queremos saber o número de horas trabalhadas entre o horário de entrada e saída, efetuaríamos o seguinte cálculo: =05:00:00-21:00:00

Ao efetuar este cálculo obtemos um erro, pois o resultado é negativo e o sistema 1900 não suporta valores negativos. Ao utilizarmos a função ABS para envelopar a diferença, obtemos um resultado; porém, incorreto. Veja que ao passar o resultado para valor absoluto, a diferença é invertida (21-5=16). Mas sabemos que das 9PM às 00:00AM existem 3 horas e das 00:00AM às 05:00AM existem 5 horas, totalizando 8horas trabalhadas. Então, como resolver este problema?

A figura abaixo mostra dois métodos para resolver a questão:

Figura 1

No primeiro exemplo, estamos avaliando se a condição é verdadeira ou falsa. Se for falsa ela retorna o valor na célula B1 e a subtração B1-A1 é feita sem problemas. Se for verdadeira, o Excel retorna B1+1 e a subtração efetuada (B1+1)-A1, retornando o número correto de horas trabalhadas entre os dois períodos. Neste último caso, estamos acrescentando 24 horas para que o resultado leve em conta a mudança de um dia para o outro.

Pare um segundo para avaliar o que esta ocorrendo aqui... Parou? Pensou? Entendeu? Agora, imagine que esta pessoa iniciou o trabalho no dia 01/07/2004 às 21:00:00 e saiu do trabalho no dia 02/07/2004 às 5 da matina. Todos os cartões de ponto que já vi em Excel, o formato é o seguinte:

Figura 2

A fórmula funciona que é uma maravilha, pois a pessoa entrou num dia e saiu no outro. Como poucas pessoas trabalham mais de 24 horas sem parar, o método acima não apresenta problemas, mas se você precisa avaliar o número de horas que uma máquina ficou em funcionamento, por exemplo, você tem que ajustar a fórmula para levar em conta o número de dias, pois a diferença será negativa. Imagine, então, que a máquina começou a rodar às 21:00:00 do dia 01/07/04 e somente parou no dia 04/07/04 às 05:00:00.

Utilizar a função MOD não resolve, pois ela retorna o resultado dentro das primeiras 24 horas. A solução é adaptar o exemplo utilizando a função SE, como segue: =SE(C3<B2;C3+3;C3)-B2

Como a máquina rodou o dia todo nos dias 2 e 3/07/04 mais as três horas do primeiro dia e mais cinco horas do quarto dia, temos um total de horas de: 3+24+24+5, isto é, 56 horas rodando sem parar. O valor verdadeiro precisa ser adicionado a três, pois 1 é para as primeiras 24 horas e 2 para os dois dias rodados por completo.

Figura 3

Se você realmente “parou”, “pensou” e “entendeu”, você deve ter notado que esse monte de fórmulas é completamente loucura! Por quê? Simples, muito simples. Imagine se você tivesse que adaptar sua fórmula toda vez que houvesse este tipo de variação nas datas!?

A menos que você tenha uma excelente razão para separar a data das horas, coloque tudo junto! Veja o exemplo abaixo:

Figura 4 Como a data e as horas são entradas na mesma célula ao efetuar as diferenças é desnecessário aquele monte de fórmulas espalhafatosas que vocês devem ver por muitos livros por aí e até mesmo as fórmulas mostradas anteriormente.

É bem provável que você tenha gostado do método apresentado, mas tenho certeza que você dirá que mesmo assim precisa da data separadamente.

O Excel possui uma tremenda flexibilidade de formatação de datas e horas (que são discutidas no Módulo de Treinamento). Pois bem, podemos utilizá-las para eliminar a data no exemplo acima e em outra célula utilizar os mesmos valores na formatação de datas:

Figura 5

Aqui, a célula A2 é igual a célula B2 e a célula A3 é igual a célula C2. As células B2 e C3 contêm a data e horas dos dias em questão, porém ambas estão formatadas para mostrar apenas as horas. Já a coluna A está formatada para mostrar apenas a data. Não obstante, os valores efetivamente nas células continuam sendo 01/07/04 21:00:00 e 04/07/2004 05:00:00 em todas as quatro células.

Uma maneira simples, limpa, elegante e eficaz de resolver o problema.

A próxima pergunta óbvia sobre este método é ter que digitar 01/07/2004 21:00:00 na célula em questão, mas se estamos interessados em saber a data e hora neste exato momento podemos utilizar o VBA junto com a função Agora (que em VBA é a função Now) para fazer isso e não perdemos tempo com longas digitações de datas. Ou ainda utilizar atalhos de teclado para isso.

A parte de VBA é discutida em detalhes no Módulo de Treinamento.

Se subtrair é um pouco complicado, somar é bem mais descomplicado. Como o Excel automaticamente formata as horas para hh:mm:ss, quem já tentou somar horas descobriu que o somatório aparece “errado”. Na verdade, ele não está errado. O formato está correto, mas a formatação que possibilita tal visualização é que está incorreta.

Como explicado anteriormente, para mostrar horas acima de 24 horas com a visualização que esperamos basta colocar a parte referente às horas (h) entre colchetes. O exemplo da subtração de horas faz exatamente isso, caso contrário, o valor visual da célula seria diferente.

Dito isso, o somatório de horas passa a ser uma tarefa bem fácil, como mostra a figura abaixo:

Figura 6

Todas as horas vistas nesta parte referem-se as horas inteiras. Se você utilizar a função de planilha Agora o Excel colocará a data, hora, minutos e segundos na célula selecionada. Contudo, talvez não seja bem isso que você queira fazer.

Se você coleta informações sobre carros que entram e saem da garagem de seu edifício, é provável que tal precisão seja necessária. Mas, e se estamos avaliando um cartão de ponto? Neste caso, é presumível que você queira trabalhar com horas inteiras ou no máximo frações de horas, como a cada 15 ou 30 minutos.

Vamos supor que a máquina do exemplo anterior iniciou o funcionamento de acordo com a figura abaixo:

Figura 7

Em apenas um dia a máquina iniciou no minuto cravado. Todos os outros dias ela inicia em uma fração de segundo qualquer. Neste exemplo, você deseja arredondar para o minuto mais próximo. Com isso quero dizer que você deixará o Excel decidir se o arredondamento é para cima ou para baixo, tudo que fazemos é dar ao Excel os parâmetros para avaliação.

Como todos nós sabemos, o dia tem 24 horas e em 24 horas temos 1.440 minutos (24horas*60minutos). Com esta informação fica fácil avaliar o problema. Se multiplicarmos os valores observados na coluna A pelo número de minutos em um dia, estamos efetivamente movendo a fração referente aos minutos para a esquerda do decimal. Se arredondarmos este valor para zero casas decimais, estamos arredondando os segundos para cima ou para baixo. O resultado precisa, então, ser divido por 1.440 para retorná-lo para a base correta:

Figura 8

Feito o arredondamento para o minuto, para arredondar para os 15 ou 30 minutos fica fácil. Se observarmos 1.440 minutos em um dia o número de vezes que 15 minutos ocorre em um dia é 1.440/15 ou 96 vezes em um dia. Da mesma forma, 30 minutos ocorre 1.440/30 vezes ao dia (48 vezes). Por analogia, podemos expandir o conceito para qualquer outro tipo de arredondamento, por exemplo, 5 minutos ou 10 minutos.

A figura abaixo mostra o arredondamento da figura anterior para 15 e 30 minutos:

Figura 9

CONCLUSÃO

Neste breve artigo mostro como manipular alguns aspectos de datas e horas no Excel. Se você deseja aprender mais sobre a manipulação de datas e horas nas planilhas do Excel e no VBA clique no link http://shop.linhadecodigo.com.br/treinamento.asp?id=307 (apenas R$10 via download) para adquirir já o seu módulo de treinamento. Além dos exemplos acima, o leitor aprenderá o que se passa por trás dos cálculos (verá que até mesmo o Ajuda do Excel passa informação incorreta sobre onde realmente as datas iniciam em ambos sistemas, 1900 e 1904). O leitor também aprenderá sobre manipulação de datas e horas no VBA, como resolver questão do ano bissexto, último dia útil do mês, primeiro dia do mês, data móveis e muito mais. Além, é claro, de acumular pontos no programa TopDev da Microsoft.