Limpeza de dados contendo valores de data
Os dados que você obtém de diferentes fontes podem conter valores de data. Neste capítulo, você entenderá como preparar seus dados que contêm valores de dados para análise.
Você aprenderá sobre -
- Formatos de Data
- Data em formato serial
- Data em diferentes formatos de mês-dia-ano
- Conversão de datas no formato serial para o formato mês-dia-ano
- Conversão de datas no formato mês-dia-ano para o formato serial
- Obtendo a data de hoje
- Encontrar um dia útil após dias especificados
- Personalizando a definição de um fim de semana
- Número de dias úteis entre duas datas fornecidas
- Extraindo ano, mês, dia da data
- Extraindo dia da semana a partir da data
- Obtendo Data do Ano, Mês e Dia
- Calculando o número de anos, meses e dias entre duas datas
Formatos de Data
Suporta Excel Date valores de duas maneiras -
- Formato Serial
- Em diferentes formatos de ano-mês-dia
Você pode converter -
UMA Date em formato serial para um Date no formato ano-mês-dia
UMA Date no formato ano-mês-dia para um Date em formato serial
Data em formato serial
UMA Date em formato de série é um número inteiro positivo que representa o número de dias entre a data fornecida e 1º de janeiro de 1900. Ambos os Datee 1º de janeiro de 1900 estão incluídos na contagem. Por exemplo, 42354 é umDate que representa 16/12/2015.
Data em formatos de mês-dia-ano
Excel suporta diferentes Date Formatos baseados no Locale(Local) que você escolher. Portanto, você precisa primeiro determinar a compatibilidade do seuDateformatos e a análise de dados em mãos. Observe que certoDate formatos são prefixados com * (asterisco) -
Date formatos que começam com * (asterisco) respondem às mudanças nas configurações regionais de data e hora especificadas para o sistema operacional
Date formatos sem * (asterisco) não são afetados pelas configurações do sistema operacional
Para fins de compreensão, você pode assumir os Estados Unidos como o Local. Você encontra o seguinteDate formatos para escolher para o Date- 8 th junho de 2016 -
- * 8/06/2016 (afetado pelas configurações do sistema operacional)
- * Quarta-feira, 8 de junho de 2016 (afetado pelas configurações do sistema operacional)
- 6/8
- 6/8/16
- 06/08/16
- 8-Jun
- 8-Jun-16
- 08-Jun-16
- Jun-16
- June-16
- J
- J-16
- 6/8/2016
- 8-Jun-2016
Se você inserir apenas dois dígitos para representar um ano e se -
Os dígitos são 30 ou mais, o Excel assume que os dígitos representam anos no século XX.
Os dígitos são inferiores a 30, o Excel assume que os dígitos representam os anos no século XXI.
Por exemplo, 1/1/29 é tratado como 1º de janeiro de 2029 e 1/1/30 é tratado como 1º de janeiro de 1930.
Conversão de datas no formato serial para o formato mês-dia-ano
Para converter datas do formato serial para o formato Mês-Dia-Ano, siga as etapas abaixo -
Clique no Number guia no Format Cells caixa de diálogo.
Clique Date debaixo Category.
Selecione Locale. O disponívelDate formatos serão exibidos como uma lista em Type.
Clique em um Format debaixo Type para ver a visualização na caixa adjacente a Sample.
Após escolher o formato, clique OK.
Conversão de datas no formato mês-dia-ano para o formato serial
Você pode converter datas no formato Mês-Dia-Ano para o formato Serial de duas maneiras -
Usando Format Cells caixa de diálogo
Usando Excel DATEVALUE função
Usando a caixa de diálogo Formatar células
Clique no Number guia no Format Cells caixa de diálogo.
Clique General debaixo Category.
Usando a função DATEVALUE do Excel
Você pode usar o Excel DATEVALUE função para converter um Date para Serial Numberformato. Você precisa incluir oDateargumento em “”. Por exemplo,
= DATEVALUE ("6/8/2016") resulta em 42529
Obtendo a data de hoje
Se você precisar realizar cálculos com base na data de hoje, basta usar a função do Excel TODAY (). O resultado reflete a data em que é usado.
A imagem seguinte da HOJE utilização da função () foi tomada em 16 th Maio de 2016 -
Encontrar um dia útil após os dias especificados
Você pode ter que realizar certos cálculos com base em seus dias de trabalho.
Dias úteis excluem dias de fim de semana e quaisquer feriados. Isso significa que se você pode definir seu fim de semana e feriados, quaisquer cálculos que você fizer serão baseados em dias úteis. Por exemplo, você pode calcular as datas de vencimento da fatura, os tempos de entrega previstos, a data da próxima reunião, etc.
Você pode usar o Excel WORKDAY e WORKDAY.INTL funções para tais operações.
| S.No. | Descrição da função |
|---|---|
| 1 | WORKDAY Retorna o número de série da data anterior ou posterior a um determinado número de dias úteis |
| 2 | WORKDAY.INTL Retorna o número de série da data anterior ou posterior a um determinado número de dias úteis usando parâmetros para indicar quais e quantos dias são dias de fim de semana |
Por exemplo, você pode especificar o 15 º dia de trabalho a partir de hoje (imagem abaixo é tomada em 16 th maio 2016), utilizando as funções hoje e dia de trabalho.
Suponha que 25 th maio 2016 e 1 st junho 2016 são feriados. Então, seu cálculo será o seguinte -
Personalizando a definição de um fim de semana
Por padrão, o fim de semana é sábado e domingo, ou seja, dois dias. Você também pode definir opcionalmente seu fim de semana com oWORKDAY.INTLfunção. Você pode especificar seu próprio fim de semana por um número de fim de semana que corresponda aos dias de fim de semana, conforme indicado na tabela abaixo. Você não precisa se lembrar desses números, porque ao começar a digitar a função, você obtém uma lista de números e dias de fim de semana na lista suspensa.
| Dias de fim de semana | Número do fim de semana |
|---|---|
| Sábado domingo | 1 ou omitido |
| Domingo segunda-feira | 2 |
| Segunda-feira terça-feira | 3 |
| Terça quarta | 4 |
| Quarta-feira quinta-feira | 5 |
| Quinta-feira sexta-feira | 6 |
| Sexta Sabado | 7 |
| Somente domingo | 11 |
| Apenas segunda-feira | 12 |
| Só terça-feira | 13 |
| Apenas quarta-feira | 14 |
| Somente quinta | 15 |
| Apenas sexta-feira | 16 |
| Só sábado | 17 |
Suponha que se o fim de semana for apenas sexta-feira, você precise usar o número 16 na função WORKDAY.INTL.
Número de dias úteis entre duas datas fornecidas
Pode haver um requisito para calcular o número de dias úteis entre duas datas, por exemplo, no caso de calcular o pagamento a um funcionário contratado que é pago por dia.
Você pode encontrar o número de dias úteis entre duas datas com as funções do Excel NETWORKDAYS e NETWORKDAYS.INTL. Assim como no caso de WORKDAYS e WORKDAYS.INTL, NETWORKDAYS e NETWORKDAYS.INTL permitem que você especifique feriados e com NETWORKDAYS.INTL você pode especificar adicionalmente o fim de semana.
| S.No. | Descrição da função |
|---|---|
| 1 | NETWORKDAYS Retorna o número de dias úteis inteiros entre duas datas |
| 2 | NETWORKDAYS.INTL Retorna o número de dias úteis inteiros entre duas datas usando parâmetros para indicar quais e quantos dias são dias de fim de semana |
Você pode calcular o número de dias úteis entre hoje e outra data com as funções TODAY e NETWORKDAYS. Na captura de tela a seguir apresentado, hoje é 16 th maio 2016 e data final é 16 th de junho de 2016. 25 th maio 2016 e 1 st junho 2016 são feriados.
Novamente, o fim de semana é considerado sábado e domingo. Você pode ter sua própria definição para fim de semana e calcular o número de dias úteis entre duas datas com a função NETWORKDAYS.INTL. Na captura de tela abaixo, apenas sexta-feira é definida como fim de semana.
Extraindo ano, mês, dia da data
Você pode extrair de cada data em uma lista de datas, o dia, mês e ano correspondentes usando as funções do excel DIA, MÊS e ANO.
Por exemplo, considere as seguintes datas -
De cada uma dessas datas, você pode extrair dia, mês e ano da seguinte forma -
Extraindo dia da semana a partir da data
Você pode extrair de cada data em uma lista de datas, o dia da semana correspondente com a função Excel WEEKDAY.
Considere o mesmo exemplo dado acima.
Obtendo Data do Ano, Mês e Dia
Seus dados podem ter as informações sobre Ano, Mês e Dia separadamente. Você precisa obter a data combinando esses três valores para realizar qualquer cálculo. Você pode usar a função DATE para obter os valores de data.
Considere os seguintes dados -
Use a função DATE para obter os valores DATE.
Calculando anos, meses e dias entre duas datas
Você pode ter que calcular o tempo decorrido a partir de uma determinada data. Você pode precisar dessas informações na forma de anos, meses e dias. Um exemplo simples seria calcular a idade atual de uma pessoa. É efetivamente a diferença entre a data de nascimento e hoje. Você pode usar as funções DATEDIF, TODAY e CONCATENATE do Excel para este propósito.
O resultado é o seguinte -
