Resumo
Você pode configurar uma grade de calendário dinâmica em uma planilha do Excel com uma série de fórmulas, conforme explicado neste artigo. No exemplo mostrado, a fórmula em B6 é:
=start-CHOOSE(WEEKDAY(start),0,1,2,3,4,5,6)
em que "início" é o intervalo nomeado K5 e contém a data 1º de setembro de 2018.
Explicação
Nota: Este exemplo assume que a data de início será fornecida como o primeiro dia do mês. Veja abaixo uma fórmula que retornará dinamicamente o primeiro dia do mês atual.
Com o layout da grade conforme mostrado, o principal problema é calcular a data na primeira célula do calendário (B6). Isso é feito com esta fórmula:
=start-CHOOSE(WEEKDAY(start),0,1,2,3,4,5,6)
Esta fórmula calcula o domingo anterior ao primeiro dia do mês usando a função ESCOLHER para "reverter" o número correto de dias para o domingo anterior. CHOOSE funciona perfeitamente nesta situação, pois permite valores arbitrários para cada dia da semana. Usamos esse recurso para reverter zero dias quando o primeiro dia do mês é um domingo. Mais detalhes sobre este problema são fornecidos aqui.
Com o primeiro dia estabelecido em B6, as outras fórmulas na grade simplesmente incrementam a data anterior em um, começando com a fórmula em C6:
=IF(B6"",B6,$H5)+1
Esta fórmula testa a célula imediatamente à esquerda para um valor. Se nenhum valor for encontrado, ele puxa um valor da coluna H na linha acima. Nota $ H5 é uma referência mista, para bloquear a coluna enquanto a fórmula é copiada em toda a grade. A mesma fórmula é usada em todas as células, exceto B6.
Regras de formatação condicional
O calendário usa fórmulas de formatação condicional, altere a formatação para sombrear os meses anteriores e futuros e para destacar o dia atual. Ambas as regras são aplicadas a toda a grade. Para os meses anteriores e seguintes, a fórmula é:
=MONTH(B6)MONTH(start)
Para o dia atual, a fórmula é:
=B6=TODAY()
Para obter mais detalhes, consulte: Formatação condicional com fórmulas (10 exemplos)
Título do calendário
O título do calendário - mês e ano - são calculados com esta fórmula na célula B4:
=start
Formatado com o formato de número personalizado "mmmm aaaa". Para centralizar o título acima do calendário, o intervalo B4: H4 tem o alinhamento horizontal definido como "centralizado na seleção". Esta é uma opção melhor do que mesclar células, pois não altera a estrutura da grade na planilha.
Calendário perpétuo com data atual
Para criar um calendário que atualiza automaticamente com base na data atual, você pode usar uma fórmula como esta em K5:
=EOMONTH(TODAY(),-1)+1
Esta fórmula obtém a data atual com a função TODAY e, em seguida, obtém o primeiro dia do mês atual usando a função EOMONTH. Substitua TODAY () por qualquer data para construir um calendário em um mês diferente. Mais detalhes sobre o funcionamento do EOMONTH aqui.
Passos para criar
- Ocultar linhas de grade (opcional)
- Adicione uma borda a B5: H11 (7R x 7C)
- Nomeie K5 "início" e insira uma data como "1 de setembro de 2018"
- Fórmula em B4 = início
- Formatar B4 como "mmmm aaaa"
- Selecione B4: H4, defina o alinhamento para "Centralizar na seleção"
- No intervalo B5: H5, digite as abreviações dos dias (SMTWTFS)
- Fórmula em B6 = início-ESCOLHER (WEEKDAY (início), 0,1,2,3,4,5,6)
- Selecione B6: H11, aplique o formato de número personalizado "d"
- Fórmula em C6 = IF (B6 "", B6, $ H5) +1
- Copie a fórmula em C6 para as células restantes na grade do calendário
- Adicionar regra de formatação condicional anterior / seguinte (consulte a fórmula acima)
- Adicionar regra de formatação condicional atual (consulte a fórmula acima)
- Altere a data no K5 para outra data do "primeiro dia do mês" para testar
- Para calendário perpétuo, fórmula em K5 = FIM (TODAY (), - 1) +1
