Excel: Converter as datas em trimestres
o Excel tem funções incorporadas para retornar ano, Número do mês, nome do mês, número da Semana, Dia da semana ou dia de qualquer célula que contenha uma data. Mas não há forma de devolver um quarto de número. Os trimestres são mais difíceis porque a definição de um trimestre varia em função da data final do exercício.
para os anos civis
se o seu exercício terminar em 31 de dezembro, então você pode usar uma fórmula curta. (A figura 1 mostra as funções do Excel que você pode usar para converter uma data em anos, meses, semanas ou dias de semana.) Primeiro, use a função =MONTH() para converter a data para um número de mês. Divida esse resultado por 3. As datas em janeiro retornarão 0.33, fevereiro 0.67, março 1.00, abril 1.33, e assim por diante. Usando a função = ROUNDUP (), você terá 1 para janeiro, fevereiro e março; 2 para abril a junho; e assim por diante.
para mostrar o quarto como ” Q1 “ou” Qtr 1″, concatenate o texto apropriado antes do resultado. Na Figura 2, a célula A7 utiliza uma fórmula de = “Q” &ROUNDUP (mês (A2))/3,0)&”-“& ano (A2) de regresso “T1-2017.”
para outros exercícios fiscais
os exercícios fiscais que terminam em 31 de Janeiro são comuns nas empresas de retalho. Os empreiteiros do governo podem usar um ano fiscal no final de 30 de setembro. Para calcular um trimestre fiscal, primeiro converta a data para um número de mês usando =mês(). Este resultado é limitado a 12 inteiros de 1 a 12.
você poderia usar uma fórmula VLOOKUP para converter o número do mês para o número do trimestre. Mas a fórmula =VLOOKUP (mês (A2), {1,4;2,1;5,2;8,3;11,4},2) é difícil lembrar e digitar. A constante de array dentro dos suspensórios encaracolados contém uma tabela de pesquisa de 5 linhas por 2 colunas.
em vez disso, a função = CHOOSE() permitir-lhe-ia especificar 12 números trimestrais que correspondem aos meses 1 a 12. Se o seu ano fiscal terminar em janeiro, você usaria = escolha (mês (A2),4,1,1,1,2,2,2,3,3,3,4,4). Para um ano fiscal que termina em Março, use = CHOOSE (MONTH (A2)),4,4,4,1,1,1,2,2,2,3,3,3).
se preferir mostrar o quarto como Q1 em vez de 1, pode usar o amperador e concatenar um “Q” antes da função escolher. Os resultados são mostrados nas células A13 e A20 da Figura 2, com as fórmulas mostradas em D13 e D20.
adicionando um ano FISCAL ao trimestre
em algumas empresas, o ano fiscal é determinado pelo ano civil em que ocorre o 12º mês do ano. Por exemplo, um ano fiscal que decorre de fevereiro de 2017 A janeiro de 2018 teria trimestres chamados Q1-2018 a Q4-2018. Em outras empresas, o ano fiscal pode ser referido como algo como “17-18.”
no caso em que o exercício fiscal é o ano do mês final, use uma função IF() para testar se o número do mês é inferior ao mês inicial. Por exemplo, com um ano fiscal terminando em janeiro, o ano fiscal será =ano(A2) se a data for Antes de fevereiro e =ano(A2)+1 em fevereiro e mais tarde. Assim, para incluir o ano fiscal com o trimestre, você poderia usar = se (mês (A2)<2,0,1)+ano(A2).
por um ano findo em Março, você mudaria o teste <2 para < 4: = se (mês (A2)<40,1)+ano(A2). Por um ano findo em setembro, você testaria para < 10: = se (mês (A2)<100,1)+ano(A2).
a célula A21 na Figura 2 mostra como concatenar o trimestre fiscal e o ano juntos Usando esta fórmula longa: =”Q”&CHOOSE(MONTH(A17),4,1,1,1,2,2,2,3,3,3,4,4)&”-“&IF(MONTH(A17)<2,0,1)+YEAR(A17). Isso devolveria o trimestre em um formato de ” Q4-2017.”
If, instead, you need it to say 17-18, the long formula becomes even longer. Só para obter a parte do ano da fórmula, você usaria: =IF(MONTH(A2)<4,MOD(YEAR(A2)-1,100)&”-“&TEXT(A2,”YY”),TEXT(A2,”YY”)&”-“&MOD(YEAR(A2)+1,100)).
retornando um período em vez de um mês
recentemente conduzi um seminário para IMA® em Nashville. Como o governo do Estado do Tennessee tem um ano fiscal terminando em junho, eles preferiram reportar usando um número de período em vez de um mês. Julho é o período 1, Dezembro é o período 6, Janeiro é o período 7, e assim por diante. Neste caso, muitos dos colegas usaram uma tabela VLOOKUP para o lado direito dos dados. Mas eles também poderiam usar = Escolha (mês (A2),7,8,9,10,11,12,1,2,3,4,5,6) alcançar o mesmo resultado.
as datas não podem ser texto
a ressalva para todas estas fórmulas é que você está armazenando suas datas como datas reais e não como texto. Uma forma rápida de verificar: Carregue em Ctrl e na tecla Grave Accent (`) para comutar para o modo mostrar as fórmulas. As datas reais serão convertidas para um número de série como 42783. Se você ainda ver uma data de texto, as fórmulas não vão funcionar. Carregue em Ctrl+ ‘ de novo para sair de mostrar as fórmulas.
recentemente percorri uma planilha em que o gerente queria que as datas fossem exibidas como 1 de Fevereiro de 2017, 2 de Fevereiro de 2017, 3 de Fevereiro de 2017, 4 de Fevereiro de 2017. Os sufixos após a data (“st”,” nd”,” “rd” E “th”) convertem um número cardinal para um número ordinal. Enquanto o cabo de preenchimento do Excel lhe permitirá criar facilmente uma coluna de números cardinais, as fórmulas deste artigo tornam-se muito mais complexas. Uma fórmula de 41 caracteres cresceu em 2.438 caracteres, a fim de lidar com as datas de texto (Ver Figura 3).
