Neste vídeo, veremos como calcular e destacar datas de expiração.
Digamos que sua empresa tenha iniciado um programa de associação de algum tipo e seu chefe acabou de lhe enviar um conjunto de dados. Ela deu a você uma lista de 1000 pessoas que renovaram a assinatura no ano passado ou assim, e ela está procurando por várias coisas.
Primeiro, ela quer que você calcule uma data de vencimento um ano no futuro, no último dia do mesmo mês em que a associação foi renovada.
Em segundo lugar, ela quer ver quantos dias faltam para a expiração.
Terceiro, ela deseja ver o status "Expirado" para todas as associações já expiradas e "Expirando em breve" para todas as associações que expiram nos próximos 30 dias.
Finalmente, ela disse que seria bom ver os membros expirados destacados em rosa e os que expiram em breve destacados em amarelo.
Além disso - ela pode tomá-lo antes do almoço ao meio-dia?
Umm, claro.
Primeiro, vamos converter esses dados em uma Tabela Excel adequada. Isso tornará as fórmulas muito mais fáceis de inserir, já que o Excel as copiará automaticamente à medida que avançamos.
Agora vamos calcular as datas de vencimento. Eles deveriam estar no final do mesmo mês, um ano depois, mas vamos usar um hack simples primeiro, para entrar no estádio. Como você viu em vídeos anteriores, as datas são apenas números de série, então podemos apenas inserir uma fórmula que adiciona 365 dias à data renovada.
Este é um bom começo. Podemos terminar de esboçar a solução e voltar para consertar isso mais tarde.
Quando você está resolvendo um problema mais complexo no Excel, é uma boa ideia validar a abordagem geral e, em seguida, voltar aos detalhes no final. Você não quer ficar preso a uma pequena coisa logo no início, especialmente se a abordagem pode mudar.
Agora que temos uma data de validade, podemos calcular os dias restantes. Isso precisa ser atualizado automaticamente no futuro, então usaremos a função TODAY, que sempre retorna a data de hoje.
A fórmula é simplesmente E5 menos TODAY (). Quando clico em voltar, temos dias restantes antes da expiração. Números negativos indicam que uma associação já expirou.
Para o status, usaremos uma fórmula IF aninhada simples. Se os dias restantes forem menores que zero, a associação expirará. Caso contrário, se os dias restantes forem inferiores a 30, o status deve ser "Expirando em breve". Caso contrário, o status não é nada.
=IF(F5<0,"Expired",IF(F5<30,"Expiring soon",""))
Em seguida, precisamos criar regras de formatação condicional que destacam esses valores.
Primeiro, selecione os dados e defina a célula ativa no canto superior direito. Em seguida, crie uma regra de fórmula que teste a célula ativa para o valor "Expirado". A coluna deve ser bloqueada.
Agora repita o mesmo processo para associações que estão "Expirando em breve".
Isso parece bom. Precisamos apenas fixar as datas de vencimento para pousar no último dia do mês.
Bem, acontece que existe uma função legal chamada EOMONTH (para final do mês) que obtém o último dia de um mês no passado ou futuro.
A data de início é a data renovada e os meses são 12.
E aí está, tudo o que seu chefe queria, e você ainda tem tempo para uma xícara de café antes do almoço.
Curso
Fórmula BásicaAtalhos relacionados
Inserir tabela Ctrl + T ⌃ + T Expandir ou fita colapso Ctrl + F1 ⌘ + ⌥ + R Select mesa Ctrl + A ⌘ + A Mover no sentido horário célula ativa na seleção Ctrl + . ⌃ + .
