Esta sessão será um pouco massiva pois busca explicar e exemplificar a transformação de datas\hora de diversas maneiras e em diversos conteúdos gerenciáveis, essas transformações são essenciais para quem deseja dominar a ferramenta de Planilhas do Google pois carregam o fundamento para se trabalhar com dados em Modelo, neste caso o modelo de Data/Hora.
Conhecendo as funções de Data/Hora
Inicialmente vamos entender um pouco como funciona a Planilha do Google. Operações matemática são simples mesmo trabalhando com datas, é possível efetuar operações de Soma, Subtração, Divisão ou Multiplicação por exemplo, sem qualquer comando extra além dos que já vimos na Aula 2. O vídeo abaixo exemplifica este uso, detalhe neste tópico é que embora a aplicação funcione bem com datas atuais ele tem sérios problemas com datas muito antigas, notem que ao Multiplicar a data 01/01/2020 por 2, embora esperava-se que o resultado fosse algo como 01/01/4040 ele retornou 03/01/2140 isso porquê ele considerou a menor data como 01/01/1900 ou seja, calculou o número de dias em 120 anos, multiplicou por 2 e o resultado foi impresso como 2140 ou seja.. 240 anos à partir do ano de 1900.
Agora vamos conhecer um pouco as funções específicas para se trabalhar com Data/Hora nas Planilhas do Google, o vídeo abaixo apenas mostrará onde encontrar as ferramentas citadas abaixo.
AGORA: Ao executarmos a função =AGORA() em uma planilha do google teremos como resultado o momento atual em que inserimos o campo, este recurso é muito útil quando estamos preenchendo formulários e precisamos saber com clareza a data em que foi feita a alteração. É verdade que o google trás esta informação, no entanto, quando utilizamos os recursos de Impressão ou Criação de HTML, assim como outros formatos Off-line perdemos esta informação.
DATA: A função DATA serve para informarmos para o sistema que determinados números são data, sendo assim, precisamos passar os parâmetros corretamente para que funcione, no caso, esta função exige três parâmetros, o ANO, o MÊS e o DIA, que são passados da seguinte forma, DATA(ano; mes; dia), ou seja, se quisermos informar ao sistema que estamos trabalhando com uma data específica de 20/03/2020 precisamos passar os dados da seguinte forma: DATA(2020; 03; 20), ou então, DATA(2020; 3; 20).
DATA.VALOR: Assim como a função DATA o objetivo desta função é transformar dados em uma DATA para que possamos fazer cálculos ou obter valores específicos desta data, no entanto, não é necessário que especifiquemos os valores de Ano, Mês e Dia diretamente na função, nesta função será utilizado um campo de data em formato Texto, ou, como chamamos em linguagem de programação formato do tipo String. Por exemplo, podemos observar que a função DATA.VALOR(“20/03/2020”) retornará o total de Dias, ou, se formatarmos em Data, retornará a data informada. A String na Planilha do Google é definida através das Aspas Duplas.
DIA: Assim como a função ANO e MÊS esta serve para retornar o dia de uma data específica, ou seja, passamos um parâmetro do tipo DATA para a função e o retorno será o dia, por exemplo: DIA(DATA(2020;7;10)) terá como resultado 10.
MÊS: Assim como a função ANO e DIA esta serve para retornar o mês de uma data específica, ou seja, passamos um parâmetro do tipo DATA para a função e o retorno será o mês, por exemplo: MÊS(DATA(2020;7;10)) terá como resultado 7.
ANO:A função Ano, diferentemente de AGORA e DATA, exige que seja passado apenas UM parâmetro para funcionar, no caso, uma DATA. Esta data pode ser retirada de uma tabela ou simplesmente passada entre parenteses, a fórmula se dá assim: ANO(data) onde o retorno será apenas o ANO da data informada. Por exemplo, se passarmos =ANO(DATA(2010;3;20)) o retorno será simplesmente 2010.
TO_DATE: Agora que temos um número de dias específico podemos fazer o retorno a uma data utilizando a função TO_DATE, ou, para_data. Neste caso, basta inserirmos um número total de dias como parâmetro, por exemplo, o comando TO_DATE(43910) retornaria 20/03/2020.
DATADIF: Esta função calcula a diferença entre duas datas, precisamos estabelecer a data de início e a data de término e o resultado será de acordo com a unidade definida na função, ou seja, podemos obter o retorno em Dias, em Meses, em Anos. A função funciona da seguinte forma: DATADIF(data_de_inicio; data_de_termino; unidade), onde a data de início é uma data, podemos utilizar a função vista anteriormente DATA.VALOR para criar essa data de início e a data de término. Já a unidade ela é definida por uma abreviação de texto da unidade de tempo, por exemplo, “M” para mês, “Y” para ano, “D” para dia, além disso temos as opções de resto como: “MD”, “YM” e “YD”.
“MD”: o número de dias entre data_de_inicio e data_de_termino após a subtração dos anos inteiros.
“YM”: o número de meses inteiros entre data_de_inicio e data_de_termino após a subtração dos anos inteiros.
“YD”: o número de dias entre data_de_inicio e data_de_termino, supondo que data_de_inicio e data_de_termino não tenham mais de um ano de intervalo entre elas.
Exemplos:
DATADIF(DATA(2020; 1; 15); DATA(2020; 1; 31); “D”) retornará: 16, no caso, 16 dias.
DATADIF(“15/5/2017”; “14/5/2020”; “Y”) retornará 2 pois só será computado anos inteiros.
DATADIF(“15/5/2017”; “14/5/2020”; “YM”) retornará 11, ou seja, após a subtração do ANO que resultou em 2 a diferença em MESES foi de 11 meses.
DATAM: Retorna uma data com um adicional ou subtraindo uma quantidade específica de meses, para utilizar esta função teremos dois parâmetros, o primeiro é a data a qual queremos subtrair ou adicionar os meses e o segundo parâmetro é o total de meses que queremos adicionar ou subtrair, lembrando que para adicionar basta colocar o número e para subtrair devemos indicar o número de meses com o sinal de negativo. É possível trabalhar tanto com número de dias quanto Data especificamente como podemos observar nos exemplos abaixo.
Exemplos:
DATAM(DATA(2020;7;10); 3) retornará 10/10/2020;
DATAM(43910; -2) retornará 20/01/2020 já que 43910 dias é o mesmo que 20/03/2020, ou, DATA.VALOR(“20/03/2020”)
DAYS: Esta função retorna o número de dias entre duas datas, a função possui dois parâmetros, uma a data de início e outra a data de fim. DAYS(data_de_termino; data_de_inicio), por exemplo: DAYS(DATA(2020;7;10);DATA(2020;7;15)) retornará -5, já se invertermos a ordem das datas, por exemplo: DAYS(DATA(2020;7;15);DATA(2020;7;10)) o retorno será 5 pois a ordem importa, no caso, é a primeira data MENOS a segunda.
DIA.DA.SEMANA: Esta função tem por objetivo o retorno do dia da semana de uma determinada DATA, o retorno pode ser configurado em um segundo parâmetro opcional na função, temos por exemplo, DIA.DA.SEMANA(data; tipo) onde data é a DATA que queremos obter o dia da semana e o TIPO é um parâmetro opcional, ou seja, não é necessário para a função funcionar, vejamos abaixo detalhes sobre este parâmetro:
tipo – [ OPCIONAL – 1 por padrão ] – Um número que indica o sistema de numeração a ser usado para representar a semana. Por padrão, faz a contagem a partir de domingo = 1.
Se tipo for 1, os dias serão contados a partir de domingo e o valor de domingo será 1; portanto, o valor de sábado será 7.
Se tipo for 2, os dias serão contados a partir de segunda-feira e o valor de segunda-feira será 1; portanto, o valor de domingo será 7.
Se tipo for 3, os dias serão contados a partir de segunda-feira e o valor de segunda-feira será 0; portanto, o valor de domingo será 6.
Exemplo:
DIA.DA.SEMANA(DATA(2020;3;20);1)
DIA.DA.SEMANA(DATA.VALOR(“20/3/2020”);2)
DIA.DA.SEMANA(43910;3)
Os três exemplos correspondem a mesma data, são apenas formas diferentes de inserir esta, no caso pela função DATA, pela função DATA.VALOR e pelo número de dias, o que interessa nesse caso é o tipo, pois terá resultados diferentes em cada um destes exemplos, a data informada caiu em uma Sexta-Feira, no entanto, o retorno no primeiro exemplo é 6, no segundo é 5 e no terceiro foi 4 pois depende exclusivamente de quando vamos começar a contar o dia da semana, no primeiro começou por domingo, no segundo por segunda-feira e no terceiro por segunda-feira também no entanto, considerando esta como 0.
DIAS360: Retorna a diferença entre duas datas com base no ano de 360 dias usado em alguns cálculos de juros. Têm três parâmetros, data de início, data de término e método, que são inseridos da seguinte forma: DIAS360(data_de_inicio; data_de_termino; metodo), o método é um parâmetro opcional como pode ser observado abaixo.
metodo – [ OPCIONAL – 0 por padrão ] – Um indicador do método de cálculo de dias a ser usado.
0 indica o método dos EUA – De acordo com o método dos EUA, se data_de_inicio for o último dia de um mês, o dia do mês de data_de_inicio será alterado para 30 para efeitos de cálculo. Além disso, se a data_de_termino for o último dia de um mês e o dia do mês de data_de_inicio for anterior ao dia 30, a data_de_termino será alterada para o primeiro dia do mês posterior à data_de_termino; caso contrário, o dia do mês da data_de_termino será alterado para 30.
Qualquer outro valor indica o método europeu – No método europeu, qualquer data_de_inicio ou data_de_termino que for o dia 31 de um mês terá seu dia do mês alterado para 30.
Exemplo: DIAS360(DATA(2020;1;15);DATA(2022;3;15);1) retornará 780 dias pois corresponde a dois anos de 360 dias, ou seja, 720 + 60 dias correspondente a dois meses, pois neste cálculo todos os meses possuem o mesmo tamanho de 30 dias.
DIATRABALHO: Calcula a data de término após um número especificado de dias úteis, a fórmula contém os seguintes parâmetros: DIATRABALHO(data_de_inicio, numero_de_dias, [feriados]), onde feriados é um item opcional que corresponde a um conjunto de DATAS, mais a diante veremos como montar um conjunto de dados que é chamado também de Array\Matriz\Vetor.
Exemplos:
DIATRABALHO(43910; 30; {43942;43952}) retornará 05/05/2020 pois o dia 43910 corresponde ao dia 20/03/2020 + 30 dias úteis, ou seja, não considerando sábados e domingos e retirando os feriados que foram adicionados nos dias 21/04/2020 e 01/05/2020 retornou em 05/05/2020, lembrando também que se refere a 30 dias completados.
DIATRABALHOTOTAL.INTL: Calcula a quantidade de dias úteis entre duas datas, ou seja, retorna o número de dias úteis entre dois dias determinados, excluindo os finais de semana e feriados especificados. Temos como função DIATRABALHOTOTAL.INTL(data_de_inicio; data_de_termino; [final_de_semana]; [feriados]), onde final de semana pode ser especificado como segue abaixo:
final_de_semana – [ OPCIONAL – 1 por padrão ] – Um número ou string que representa os dias da semana considerados finais de semana.
Método por string: é possível especificar os finais de semana usando sete números 0 e 1, onde o primeiro número no conjunto representa segunda-feira e o último indica domingo. Um 0 significa que o dia é útil, enquanto 1 significa que o dia é no fim de semana. Por exemplo, “0000011” indica que sábado e domingo compõem o fim de semana.
Método por número: em vez de usar o método por string acima, é possível usar um único número. 1 = sábado/domingo são fins de semana, 2 = domingo/segunda-feira e este padrão se repete até que 7 = sexta-feira/sábado. 11 = apenas domingo é fim de semana, 12 = apenas segunda-feira é fim de semana e este padrão se repete até que 17 = apenas sábado é fim de semana.
Os feriados, é um parâmetro opcional, assim como na função DIASTRABALHO e deve ser passada como ARRAY.
Exemplos:
DIATRABALHOTOTAL.INTL(DATA(2020; 3; 20); DATA(2020; 5; 4); 1; {43942;43952}) retornará 30.
DIATRABALHOTOTAL.INTL(DATA(2020;3;20); DATA(2020;5;4);”0010011″; {43942;43952}) retornará 24 pois considerará quarta, sábado e domingo como finais de semana.
DIATRABALHOTOTAL: Funciona de forma semelhante ao DIATRABALHOTOTAL.INTL com a única diferença de não podermos especificar quais são os dias do final de semana, nesse caso, será considerado sempre sábado e domingo. É uma forma mais simplificada de se fazer o processo e servirá para a grande maioria dos casos no Brasil. A função se dá da seguinte forma DIATRABALHOTOTAL(data_de_inicio; data_de_termino; feriados), como exemplo podemos observar DIATRABALHOTOTAL(DATA(2020; 3; 20); DATA(2020; 5; 4); {43942;43952}) que retornará 30 como no exemplo proposto acima.
FIMMÊS: Retorna a data referente ao último dia de um mês que cai em determinado número de meses antes ou depois de outra data. FIMMÊS(data_de_inicio; meses), ou seja, se quisermos o último dia do mês da data específica fazemos por exemplo: DATA(2020; 3; 20) que retornará a data completa 31/03/2020; Já se quisermos retornar a data de um mês depois devemos fazer: FIMMÊS(DATA(2020; 3; 20); 1) que retornará 30/04/2020.
FRAÇÃOANO: Retorna o número de anos, incluindo frações de anos, entre duas datas usando determinada convenção para a contagem de dias. Para isso devemos passar da seguinte forma: FRAÇÃOANO(data_de_inicio, data_de_termino, [convencao_de_calculo_de_dias]). Onde a convenção de cálculo de dias é um parâmetro opcional que deve ser feita da seguinte forma:
convencao_de_calculo_de_dias – [ OPCIONAL – 0 por padrão ] – Um indicador do método de cálculo de dias a ser usado.
- 0 indica o método 30/360 dos EUA (NASD) – Pressupõe meses de 30 dias e anos de 360 dias conforme os padrões da Associação Nacional dos Corretores de Valores Mobiliários dos EUA (NASD, na sigla em inglês) e realiza ajustes específicos nas datas inseridas que caem no final do mês.
- 1 indica real/real – Faz o cálculo com base no número real de dias entre as datas especificadas e no número real de dias nos anos envolvidos. Usado para títulos e letras do Tesouro dos EUA, mas também o mais relevante para uso não financeiro.
- 2 indica real/360 – Faz o cálculo com base no número real de dias entre as datas especificadas, mas pressupõe um ano de 360 dias.
- 3 indica real/365 – Faz o cálculo com base no número real de dias entre as datas especificadas, mas pressupõe um ano de 365 dias.
- 4 indica 30/360 da Europa – Similar a 0, faz o cálculo com base em um mês de 30 dias, mas ajusta as datas de final de mês segundo as convenções financeiras da Europa.
Exemplo: FRAÇÃOANO(DATA(2017;3;20);DATA(2020;5;5);1) retornará 3,127831425 anos.
HOJE: A função HOJE retorna o dia atual em forma de DATA, ou seja, é bastante útil quando queremos saber a diferença entre uma determinada data e HOJE como fazemos no cálculo de Data de Nascimento para descobrir a Idade de uma pessoa. A função, assim como a AGORA não possui parâmetros e se dá da seguinte forma HOJE(), uma observação importante quanto a esta função é que sempre representará a data da última vez em que a planilha foi recalculada, em vez de permanecer na data em que ela foi introduzida pela primeira vez, então cuidado redobrado ao utilizar esta fórmula.
TEMPO: Semelhante a função DATA que serve para criar um objeto do tipo data, a função tempo serve para criarmos à partir de dados como Hora, Minuto e Segundo um objeto do tipo horário, que será utilizado para efetuarmos cálculos de horários. A fórmula se dá no seguinte formato, TEMPO(hora; minuto; segundo). Exemplo: TEMPO(10;5;30) retornará 10:05:30.
VALOR.TEMPO: Da mesma forma que o DATA.VALOR retorna em DIAS uma determinada data em STRING a função VALOR.TEMPO converte um horário em STRING em DIA, como podemos observar no exemplo: VALOR.TEMPO(“12:00:00”) que retornará 0,5 correspondente a 0,5 dia.
HORA: A função HORA funciona de forma semelhante a ANO,MÊS e DIA, serve para buscar uma hora específica dentro de um objeto do tipo horário;
Exemplos:
HORA(TEMPO(11;40;59)) retornará 11;
HORA(AGORA()) retornará a hora atual;
HORA(“20:49:59”) retornará 20;
MINUTO: Assim como a função HORA esta retornará o minuto específico de um horário, por exemplo, se tivermos MINUTO(TEMPO(11;40;59)) o retorno será 40, pois se trata de um horário onde o ponteiro do relógio estava em 40 minutos.
SEGUNDO: Assim como a função HORA e MINUTO esta retornará o segundo específico de um horário, por exemplo, se tivermos SEGUNDO(TEMPO(11;40;59)) o retorno será 59, pois se trata de um horário onde o ponteiro do segundo do relógio estava em 59 segundos.
ISDATE: A função ISDATE retorna se uma determinada STRING é uma data ou não, por exemplo, se tivermos: ISDATE(“30/05/2020”) o retorno será VERDADEIRO, no entanto, se a data for uma data inexistente ou mal formatada como nos exemplos: ISDATE(“30/02/2020”) e ISDATE(“30/005/2020”) o retorno será FALSO.
NÚMSEMANA: Retorna o número de semanas desde o início do ano de uma determinada data, por exemplo, se tivermos NÚMSEMANA(“30/05/2020”) o retorno será 22, ou seja, teve 22 semanas desde o início do ano até a data informada.
Para mais informações sobre as fórmulas observadas nesta sessão segue abaixo um link de material de apoio: