OS MELHORES VÍDEOS

 

Pesquisar

sexta-feira, 14 de outubro de 2011

Aprenda a calcular datas usando fórmulas no Excel 2007 / 2010



Lição 1: o que as datas significam para o Excel


Texto explicativo 1 Com o que uma data se parece para você.
 
Texto explicativo 2 Com o que uma data se parece para o Excel.
 
 
Há uma diferença entre o que as datas significam para você e o que elas significam para o Excel. Para você, 22/08/2010 é um dia, um mês e um ano. Para o Excel, 22/08/2010 é o número de série 40412. Nesta lição, você aprenderá como a exibição de datas do Excel torna possível realizar a aritmética de datas e como inserir datas em um formato que o programa possa compreender. Aprender um pouco sobre datas antes de fazer os cálculos fará com que você poupe um bom tempo mais para a frente.
 

O calendário começa em 1900

 

 

Datas são armazenadas no Excel como números de série.

As datas são armazenadas no Excel como números de série começando com 1, que representa 1 º de janeiro de 1900, que é quando o calendário do Excel começa. Cada dia depois desse acrescenta um número à sequência. Por exemplo, a data 2 de janeiro de 1900 é armazenada como 2.
Isso significa que, se você digitar 22/08/2010, o Excel armazenará a data como 40412 — ou 40411 dias a partir de 1° de janeiro de 1900.
Armazenar datas como números de série permite que o Excel realize a aritmética de datas. Para encontrar o número de dias entre duas datas, por exemplo, o Excel subtrai um número de série do outro.
Observação    Não é possível usar datas anteriores a 1 º de janeiro de 1900 em fórmulas do Excel.
 

Como o Excel reconhece datas

Texto explicativo 1 Selecione Data na lista Categoria .
Texto explicativo 2 Selecione um formato de data na lista Tipo .
 

 

Mesmo que você digite 22/08/2010, 22-ago-2010 ou 22 de agosto de 2010, essa data é armazenada como o número de série 40412. Às vezes, o Excel poderá surpreender você trocando o formato de data digitado pelo seu formato padrão. Mas, independentemente do formato, o número de série da data é 40412.
Haverá uma exceção se você usar um formato não reconhecido como data pelo Excel. Por exemplo, se tiver digitado agosto-22, 2010 ou 8,22,10, o Excel poderá armazenar essas informações como texto sem formatação e não como um número de série.
Para alterar o formato — ou confira algumas das outras maneiras de usar barras ou hífens para formatar datas — abra a caixa de diálogo Formatar Células, mostrada na figura, clicando com o botão direito em uma célula contendo uma data e escolhendo Formatar Células. Como alternativa, você pode saltar à direita para a prática, onde verá e testará o processo para si mesmo.




Lição 2: calcular datas usando fórmulas


Função Usada para localizar
DIATRABALHOTOTAL Número corrente de dias úteis entre duas datas
DIATRABALHO A data situada em um número especificado de dias úteis após outra data
DATA A data situada em um número especificado de anos, meses ou dias após outra data
HOJE Data atual


Essas funções expressam fórmulas longas rapidamente.

Para fazer cálculos aritméticos no Excel, você usa fórmulas. Nesta lição, você aprenderá a usar uma fórmula simples para encontrar o número de dias entre duas datas e verá por que é muito importante digitar datas de forma que o Excel possa reconhecê-las. Você também aprenderá a usar as fórmulas pré-escritas, chamadas de funções, para fazer outros tipos de cálculos de data, como descobrir a sua idade hoje em números de dias. Na sessão prática ao final da lição, você terá a oportunidade de usar as fórmulas que aprendeu.


Encontrar o número de dias entre duas datas




Texto explicativo 1 Fórmula na planilha.
Texto explicativo 2 Resultado da fórmula.
Texto explicativo 3 Fórmula na barra de fórmulas.
 
Imagine que hoje é 9 de junho de 2010. Suas férias começam em 21 de agosto de 2010, e você deseja descobrir quantos dias faltam para as férias. Para fazer o cálculo, digite uma fórmula simples em uma célula vazia:
=A3-A2
Há 73 dias entre as duas datas. Você pode usar as datas em uma fórmula porque o Excel reconhece os valores nas células A2 e A3 pelo formato e os armazena como números de série. O Excel subtrai o número de série para 9/6/2010 do número de série para 21/9/2010: 40411 menos 40338 = 73.
O sinal de igual (=) informa ao Excel que se trata de uma fórmula. O operador de sinal de menos (-) instrui o Excel a subtrair um valor do outro. A3 e A2 são as referências de célula que estão relacionadas aos valores nessas células.
Você perceberá que, depois de digitar o sinal de igual (=), o recurso AutoCompletar Fórmula exibe uma lista de nomes de funções para a sua escolha, com base em suas estimativas sobre o que você está fazendo. Como essa fórmula não usa uma função, basta continuar digitando para ignorar AutoCompletar Fórmula.
Se você quiser saber mais tarde como obteve o resultado da fórmula, a fórmula estará visível na barra de fórmulas, próxima à parte superior da planilha, sempre que você selecionar a célula que a contém. Também é possível clicar duas vezes na célula para ver a fórmula nessa célula.
Observação     O resultado da fórmula pode aparecer em formato de data, já que os valores em A2 e A3 estão em formato de data. Na sessão prática ao final da lição, você verá como reformatar o resultado rapidamente, para que ele apareça como um número.


Encontrar o número de dias úteis entre duas datas


 

Texto explicativo 1 Fórmula na planilha.
Texto explicativo 2 Resultado da fórmula.
Texto explicativo 3 Fórmula na barra de fórmulas.

Não é possível encontrar o número de dias úteis (de segunda à sexta) entre duas datas apenas subtraindo uma data da outra. Esse procedimento requer uma fórmula pré-escrita, chamada de função DIATRABALHOTOTAL, que calcula o número corrente de dias úteis entre duas datas.
Ainda é 9 de junho de 2010, e suas férias ainda começam em 21 de agosto de 2010; mas, agora, você deseja descobrir quantos dias úteis faltam para as férias. Para isso, digite a fórmula:
=DIATRABALHOTOTAL(A2;A3)
Há 53 dias úteis até as férias. Os valores nas células A2 e A3 são os argumentos, informações que indicam à função DIATRABALHOTOTAL o que deve ser calculado. Parênteses separam a função dos argumentos. Dentro dos parênteses, uma vírgula separa os argumentos entre si.
Observação     Depois que você digita um sinal de igual (=) e as letras iniciais de um nome de função, o Excel exibe uma lista de funções. Para deixar que o Excel digite o nome para você, selecione DIATRABALHOTOTAL na lista. Em seguida, o recurso AutoCompletar Fórmula exibirá os nomes de argumentos de função. Você pode concluir a fórmula usando AutoCompletar Fórmula ou pode ignorar esse recurso e continuar digitando por conta própria.


Encontrar a data após um número de dias úteis

Usar a função DIATRABALHO 
 
Texto explicativo 1 Fórmula na planilha.
Texto explicativo 2 Resultado da fórmula.
Texto explicativo 3 Fórmula na barra de fórmulas.

Suponha que você precise encontrar uma data, como a data de vencimento de uma nota fiscal ou a data de término de um projeto que ocorre um número de dias úteis (de segunda à sexta) antes ou após uma determinada data. É fácil fazer isso usando a função DIATRABALHO.
Imagine que você tenha aproximadamente 80 dias úteis para concluir um projeto que começa em 31/12/2010 e precise encontrar a data de término desse projeto. Primeiro, é necessário inserir esses valores nas células A2 e A3, como mostra a figura. Em seguida, insira as datas de todos os feriados capazes de afetar o cálculo, com cada feriado em sua própria célula. Em seguida, digite a fórmula em uma célula vazia:
=DIATRABALHO(A2;A3;A4:A6)
O projeto deve ser concluído até 22/04/2011. O Excel adicionou 80 dias (célula A3), excluindo automaticamente os fins de semana e os feriados no período listado no argumento A4: A6 (o caractere de dois pontos indica um intervalo de células). Uma vírgula separa cada argumento individual. Os parênteses separam os argumentos da função.



Encontrar a data após um número de meses

Usar a função DATA para encontrar a data após um número de meses 
 
Texto explicativo 1 Fórmula na planilha.
Texto explicativo 2 Resultado da fórmula.
Texto explicativo 3 Fórmula na barra de fórmulas.

Digamos que você tenha aproximadamente 25 meses a partir de 9/6/2011 para concluir um projeto e precise encontrar a data de término desse projeto. É possível fazer isso usando a função DATA, que possui três argumentos: ano, mês e dia.
No nosso exemplo, depois de inserir 25 na célula B2, você deve digitar esta fórmula na célula A4:
=DATA(2011,6+B2,9)
2011 é o argumento de ano, 6 mais o valor na célula B2 é o argumento de mês, enquanto 9 corresponde ao argumento de dia. Vírgulas separam os argumentos, e parênteses envolvem todos eles.
O projeto termina em 9/7/2013. Como você digitou a referência de célula (B2) em vez do valor na célula (25), o Excel poderá atualizar automaticamente o resultado se o valor for alterado. Por exemplo, se o período de tempo for alterado de 25 para 23 meses, você poderá obter a data revisada 9/5/2013 alterando o valor na célula B2 de 25 para 23, sem digitar novamente a fórmula.


Encontrar a data após um número de anos, meses e dias

Usar a função DATA por anos, meses e dias 
 
 
Texto explicativo 1 Fórmula na planilha.
Texto explicativo 2 Resultado da fórmula.
Texto explicativo 3 Fórmula na barra de fórmulas.

Imagine que você tenha recebido uma estimativa de 1 ano e 7 meses e meio (15 dias) para concluir um projeto e precise encontrar a data de término desse projeto. Para isso, use novamente a função DATA, inserindo esses valores nas células, como mostra a figura, e digitando esta fórmula na célula A6:
=DATA(2011+B2,6+B3,9+B4)
2011 mais o valor na célula B2 é o argumento de ano, 6 mais o valor em B3 corresponde ao argumento de mês, enquanto 9 mais o valor em B4 corresponde ao argumento de dia. Vírgulas separam os argumentos, e parênteses envolvem todos eles.
O projeto termina em 24 de janeiro de 2013. Como na fórmula anterior, como você digitou as referências de célula B2, B3 e B4, em vez dos valores propriamente ditos, o Excel poderá atualizar automaticamente o resultado se esses valores forem alterados. Se você recebesse mais ou menos tempo para concluir o projeto, o Excel revisaria automaticamente o resultado quando um valor nas células B2, B3 ou B4 fosse alterado.


Descobrir quantos dias de idade você tem hoje

Usar a função HOJE 
 
A função HOJE não tem argumentos.

A função HOJE é incomum. Trata-se de uma das poucas funções do Excel que não requer nenhum argumento, embora ela precise dos parênteses de abertura e fechamento: =HOJE()
HOJE insere a data atual, que é atualizada sempre que uma pasta de trabalho é aberta ou recalculada.
Apenas por diversão, você pode usar a função HOJE para descobrir quantos dias de idade você tem hoje. Esse cálculo será mostrado na sessão prática.






24 comentários:

  1. Se eu tenho várias datas quaisquer..
    10/05/2012
    12/06/2012
    25/08/2012

    como faço pra calcular a média apenas dos dias dessas datas?

    valeu

    ResponderExcluir
    Respostas
    1. Bom, você utilizará duas funções: MÉDIA e DIA.

      Partindo do exemplo citado acima:
      A1 = 10/05/2012
      A2 = 12/06/2012
      A3 = 25/08/2012
      A4 = Célula que criaremos a fórmula da média das datas

      A fórmula é a seguinte:
      =MÉDIA(DIA(A1);DIA(A2);DIA(A3))

      A função DIA armazena exatamente a posição dia dentro de um formato de data, o mesmo ocorre na função MÊS e ANO. O resultado das datas acima é quebrado, se quiser arredondar, você poderá usar o ARRED ou MARRED.

      Excluir
  2. Socorro!
    Estou trabalhando em um foumulário usando Mala Direta.Digitei a data base e preciso do calculo + 10 dias.Usei a formula "=SOMA(A2+10). Na base de dados deu certo, poremquando vai para o formulário a data soma 10 ao mês e ao invéz de 10/09/12---20/09/12 aparece 9/20/12.
    Onde estou errando?
    Esperando resposta, Rita

    ResponderExcluir
    Respostas
    1. Acho que vc deve verificar a formatação da data em seu pc, Rita, acho que a data esta sendo mostrada no padrão americano. mm/dd/aaaa.. e deve ser dd/mm/aaaa. Espero que seja somente isso..

      Excluir
    2. Infelismente a data está certa. Mesmo assim, obrigada.

      Excluir
  3. Caro Rodrigo,

    Estou precisando copiar fórmula para data, porem gostaria que só alterasse o mês e o ano mantendo o dia constante para uma planilha de financiamento (vencimento sempre no mesmo dia do mês seguinte).
    É possível ou deverei digitar data por data? Qdo é uma planilha tudo bem, imagina fazer isso o dia todo e com datas diferentes.
    Agradeço desde já.
    Se possível envie para meu e-mail gilmar@romanetti.net ou www.romanetti.net

    ResponderExcluir
    Respostas
    1. Caro Ricardo,

      Consegui a resposta ao meu questionamento.

      Segue a dica:

      Se for para o excel 2003, vc precisa instalar suplementos, da seguinte forma:
      1.No menu Ferramentas, clique em Suplementos.
      2.Na lista Suplementos disponíveis, selecione a caixa Ferramentas de análise e clique em OK.

      Se estiver usando excel em português use a função DATAM(Célula onde esta a data;1) exemplo =DATAM(B1;1)

      Se estiver em ingles use EDATE(célula;1)

      De qq forma agradeço

      Excluir
  4. Olá pessoal fiz uma planilha com data porem quando eu copio para o resto da planilha as data tambem vão como faço pra levar só a formula e não as data , ou seja só inserir daddos quando necessitar

    ResponderExcluir
  5. Prezados, boa tarde!

    Preciso descobrir quanto de cada dia da semana existe em um período determinado.

    Por exemplo: preciso contar quantas segundas, terças, quartas, quintas e sextas existem de 10/03/2012 a 25/06/2012.

    O Excel tem alguma função para esse fim?

    Obrigado.

    ResponderExcluir
  6. Gostaria de uma formula para uma planilha de desconto de cheque para o dia do vencimento do cheque (se acaso o dia do vencimento for um feirado ou um fim de semana)cair no próximo dia útil, tenho uma planilha mais só da o total de dias restante para o vencimento do cheque. Ex: o dia do vencimento do cheque é 02/11/2012 sexta-feira feriado de finados o próximo dia útil é 05/11/2012 segunda-feira.
    Como faço pra ja sair para o p´roximo dia útil. Obrigado!

    ResponderExcluir
  7. Gostaria da formula para calcular hora entre 2 intervalos, porém só contabilizar horas úteis (9:00 às 17:30). Minha célula tem o seguinte formato dd:mm:aa hh:mm. O trabalho pode começar dia 2 e terminar dia 4, por exemplo. De 2º a 6ª exeto feriado.
    09/12/2012 10:00 10/12/2012 16:30 = ? hoas


    Grato

    Antonio
    agentil@gruporw.com.br

    ResponderExcluir
  8. meu caro, estou com um problema.
    Tenho uma planilha indexada por data. E eu quero achar quantas ocorrências (linhas) durante o ano de 1946, por exemplo. Ocorre que o meu argumento de pesquisa é o ano de 1946 e se eu colocar no cont-se 1946 como a data (que é o local da pesquisa) está formatada como data o excel não reconhece a pesquisa. Mesmo que para mim aparece 07/01/1946.

    ResponderExcluir
  9. Tenho uma planilha com datas e preciso ser avisada quando essas datas venceram o prazo de 15 dias

    ResponderExcluir
  10. Tenho uma data XX/XX/XXXX, como faço para calcular essa data menos 6 meses (preciso que seja 6 meses e não 180 dias), ou seja XX/XX/XXX - 6 meses. Obrigado

    ResponderExcluir
  11. Tenho uma planilha com mais de cinco mil datas, como localizar uma data menor que: exemplo 1937.

    ResponderExcluir
  12. Desculpem o incomodo, mas estou com um problemão e não consigo resolver..
    Preciso calcular numero de avos (meses) entre datas, mas contando tanto na data de inicio como na dat final os 15 primeiros dias contando como mês trabalhado.
    ex.:
    Data inicio: 11/03/2013 Data final: 16/04/2013
    Todas as furmulas que tentei resulta em 1 ou 1,16 e na verdade se contar dias trabalhados deve trazer 2 meses.

    por favor alguém pode me ajudar ??

    ResponderExcluir
  13. 15 trabalhdos considera 1 mes

    ResponderExcluir
  14. Necessito de uma fórmula, intervalo de datas ( data Inicial / data final), com a inter-relação de outra coluna apurando a quantidade de dias decorridos na ausência de solução (data final, no caso= zero). Trata-se de processos em trâmite, que será sanado com a colocação da data final; Entretanto, dentre um arquivo com inúmeros processos nem todos poderá ser preenchida a data final, que por extensão os remanescentes é o filtro necessário (em quantidade de dias sem a devida solução).
    Utilizei a fórmula (=Se(F7=0;"";(dias360(hoje);E7 )))) , onde (F7=coluna data de retorno) e (E6= Data Inicial). Simplesmente o valor apurado representa a diferença entre as data Inicial e Retorno, não considerando no caso, a dedução se já houve retorno que é igual a zero (F7)
    Agradecido desde já.
    Antônio P Viegas - Santos/SP

    ResponderExcluir
  15. Olá,

    Estou precisando muito desse Cálculo de vencimento da planilha EXCEL 2007,
    sendo que este cálculo a baixo, é em dias totalizando um ano.
    Ex: Tenho um Extintor de Incêndio e a sua recarga foi realizada em 01/01/2012 e a próxima recarga será realizada em 01/01/2013,
    esse cálculo é de 2012 à 2013.

    =SE((AJ10>0);SE((DIAS360(HOJE();(AJ10+365))<1);"Vencido";SE((DIAS360(HOJE();(AJ10+365))<31);(DIAS360(HOJE();(AJ10+365))&" Dias para vencer");"Válido"));"")

    Eu estou querendo esse mesmo cálculo, desse mesmo jeito, agora sendo no intervalo de 5 anos.
    Ex: Tenho um Extintor de Incêndio e o seu teste Hidrostático foi realizada em 01/01/2012 e o seu próximo teste será realizado em 01/01/2017.
    o tempo corrido foi de 5 anos entre 2012 à 2017.

    Eu tentei fazer mais não conseguir, alguém tem como fazer e me mandar por e-mail.
    Meu e-mail é: joaotstbarbosa@hotmail.com

    ResponderExcluir
  16. Olá
    O que eu preciso é bem simples, preciso fazer lançamentos de cheques pré datados, que me informe quando as datas estão próximas para vencer 5 dias, 7 dias, hoje e atrasadas, utilizando as cores para demonstrar.

    Agradeço desde já,

    ResponderExcluir
  17. Fiz uma planilha de controle de férias e quando calculo os dias de férias usufruídos, (Dia fim - Dia Início) não é considerado o dia fim inclusive, onde sou obrigada a somar mais 1 no resultado final. Existe alguma formula para retornar os dias de férias? Grata.

    ResponderExcluir
  18. Considerando a data 13.01 + 120 dias, teremos o dia 13.05 (terça-feira). Como criar no excel pra quando cair na terça-feira retornar sempre ao início da semana, ou seja "segunda-feira"?

    ResponderExcluir
  19. Bom dia,
    Tenho uma data ex: 10/08/2013, onde um funcionário foi admitido.
    Que está no D10. Na coluna E10 está "Dias para vencer".
    Após 1 ano essa data vencerá, e ele precisará fazer um exame.
    Preciso de uma fórmula que na coluna E10 calcule automaticamente quanto tempo falta e se ainda não estiver vencido esteja escrita "em dia", e quando vencer ela automaticamente escreva "Vencido".


    Me ajudeeeeeem

    ResponderExcluir
    Respostas
    1. me passe um email com a planilha com a planilha.
      email(wagner_urty@hotmail.com)

      Excluir