Como configurar a formatação condicional no Excel para datas

Formatação Condicional no Excel pode ser considerada um utilitário valioso que pode ajudar a aprimorar visualmente a análise e apresentação de dados na planilha, aplicando uma formatação específica com base em regras ou condições predefinidas. Esta postagem irá compartilhar como você pode configurar a formatação condicional no Excel para datas. Também incluímos exemplos detalhados que você pode experimentar enquanto lê o guia.

Como configurar a formatação condicional no Excel para datas

A formatação condicional não apenas ajuda a destacar as células usando códigos de cores, mas também formata as linhas, colunas e células que consistem em datas de uma maneira que pode ser considerada visualmente significativa e atraente.

1. Pré-definido
2. Usuário definido
3. Crie uma regra de formatação personalizada
4. Destacando feriados no Excel
5. Formatação condicional de datas com base em múltiplas condições
6. Destacando as próximas datas e atrasos

Certifique-se de examinar os detalhes no Excel com cuidado. Oferece muitas opções, algumas das quais podem estar além do escopo deste guia.

1]Regras de data de formatação condicional predefinidas com datas

A formatação condicional com datas pode ser benéfica ao lidar com conjuntos de dados que incluem informações relacionadas a datas. O Excel oferece quase dez opções diferentes para formatar células com base em datas, conforme ilustrado abaixo:

• Para formatar uma célula com a data como tipo de dados, clique em Página inicial > Formatação Condicional > Destaque Regras da célula > Data de ocorrência

• As opções de formatação condicional podem ser escolhidas nos menus suspensos da próxima janela. O primeiro menu suspenso nos permite selecionar o intervalo de dias e as opções de formatação podem ser escolhidas no segundo.

• Clique em OK para aplicar as regras de formatação escolhidas.

2]Regras de data de formatação condicional definidas pelo usuário com datas

Além de um conjunto de regras predefinidas, o Excel também oferece a opção de personalizar essas regras com base nas necessidades do usuário. Existem várias maneiras de definir as personalizações, conforme ilustrado abaixo:

Usando formato personalizado

1. Abra a janela de formato Data de ocorrência seguindo as etapas mencionadas acima.
2. Depois de selecionar o intervalo de dias, selecione Formato personalizado no menu suspenso de opções de formatação.
3. A janela Formatar células apareceria devido ao exposto acima, onde podemos escolher a cor, a borda, a fonte e os efeitos de nossos escolha e clique em OK para aplicar as alterações.

Crie novas regras

Este método envolve a criação de uma regra inteiramente nova para formatação condicional, ao contrário do método acima, onde podemos escolher entre algumas regras predefinidas e criar apenas um formato personalizado. Criar uma nova regra envolve:

• Selecionar as células às quais a formatação será aplicada.
• Na guia Página inicial, clique em Formatação condicional > Nova regra, que deve abrir a janela Nova regra de formatação.

• Na referida janela, podemos escolher entre os Tipos de regras disponíveis, seguido da edição da descrição da regra para configurar os critérios para condicional formatação.
• Depois de concluído, o botão Formatar pode ser clicado no canto inferior direito da janela para escolher a formatação da referida regra.
• Na janela Formatar células que se abre, podemos escolher a fonte, a borda, a cor da célula, os efeitos, etc., conforme escolha e clique em OK uma vez nesta janela, seguida pela janela Nova regra de formatação para que as alterações sejam aplicadas.

3] Como criar uma regra de formatação personalizada com datas

No exemplo acima, navegamos pelas etapas para criar uma nova regra para formatação condicional com base em critérios já fornecidos. O Excel também permite aplicar as referidas regras de formatação a critérios definidos pelo usuário.

Por exemplo, suponha que precisemos destacar as células com datas anteriores a 30 ou 60 dias. Nesse caso, o mesmo também pode ser implementado aplicando fórmulas e funções de datas disponíveis no Excel, conforme ilustrado a seguir:

A] Destacar datas atuais e mais antigas

Em Selecionar uma nova regra na opção Formatação condicional, escolha Usar uma fórmula para determinar qual célula para formatar, digite:

=$H4=TODAY() para destacar os dias atuais

=$H4>TODAY() para destacar os dias futuros

=$H4<TODAY() para destacar os dias anteriores

=$H4<=(TODAY()-30) para destacar datas com mais de 30 dias.

=$H4<=(TODAY()-60) para destacar datas com mais de 60 dias.

A fórmula acima verifica o intervalo de datas selecionado para a data atual e, assim que a correspondência for encontrada, altere a cor de fundo para Roxo conforme selecionado na opção Formato. O $ usado ao lado de H significa que a coluna permaneceria constante, mas as linhas seriam variáveis.

• Depois que a descrição da regra for mencionada, a fonte, as cores e os efeitos poderão ser escolhidos clicando no botão Formatar.
• Clique em OK na janela Formatar células, seguido do mesmo na janela Nova regra, para que as alterações tenham efeito.< /span>

B] Destaque com base em um intervalo de datas definido pelo usuário

Métodos semelhantes podem ser aplicados para formatar os intervalos de datas especificados pelos usuários. Por exemplo, se precisarmos destacar as datas com mais de 30 dias, mas menos de 45 dias, a função TODAY() pode ser usada em combinação com um operador lógico como AND para especificar os critérios que envolvem duas condições:

• Selecione as células às quais a formatação precisa ser aplicada e abra a janela Nova Regra.
• No campo Editar a descrição da regra, insira a fórmula abaixo,

=AND(H2>=(TODAY()-45),H2<=(TODAY()-30))

A fórmula acima usa o operador AND para destacar em amarelo as datas com mais de 30 dias, mas menos de 45 dias para o seleção inteira, começando pela célula no. H2.

C] Destacando fins de semana

No mesmo intervalo, utilizando a fórmula WEEKDAY(), também podem ser destacadas as datas que caem em finais de semana:

• Selecione as células às quais a formatação precisa ser aplicada e abra a janela Nova Regra.
• No campo Editar a descrição da regra, insira a fórmula abaixo,

=WEEKDAY($H2, 2)>5

A sintaxe da fórmula Weekday pode ser representada como WEEKDAY(serial_number, [return_type]), em que serial_number representa o número da célula do data que estamos tentando verificar (SA1 neste caso).

O parâmetro return_types representa o tipo da semana que começa de segunda(1) até domingo(7), considerando o dia de início da semana como segunda-feira (representado por 1) e o último dia da semana como domingo (representado por 7).

No entanto, return_types são opcionais na fórmula e são representados entre chaves. >5 é usado para destacar sábado(6) e domingo(7) para o tipo de retorno especificado.

D] Destacando linhas com base em uma data específica

Esta opção pode ser útil ao lidar com grandes quantidades de dados em que uma data específica precisa ser destacada. O mesmo pode ser conseguido determinando o valor numérico da data ou usando a função DATEVALUE(). O valor numérico deve ser determinado inicialmente para aplicar a formatação condicional para uma data específica, pois o Excel armazena permanentemente as datas como números de série sequenciais. Para encontrar o valor numérico de uma data específica,

• Clique com o botão direito na célula com a data, clique em Formatar células >Número, e escolha Geral. Um valor numérico de 5 dígitos seria exibido na célula, que pode ser anotado para referência futura.

• Clique em Cancelar assim que o número for anotado, pois não precisamos alterar o formato da data da célula.
• Clique em Formatação Condicional>Nova Regra> Selecionar uma Nova Regra> Use uma fórmula para determinar qual célula formatar e digite a seguinte fórmula,

=$G5=45249

• Usando a função DATEVALUE() , o mesmo também pode ser feito inserindo a fórmula mencionada abaixo,

=$G5=DATEVALUE("11/19/2023")

O $ usado ao lado de C significa que a coluna permaneceria constante, mas as linhas seriam variáveis.

4] Destacando feriados no Excel

Como os feriados podem variar de acordo com dados demográficos, o Excel não tem nenhuma função integrada para destacar feriados. Portanto, para identificar os feriados, precisamos registrar as datas em uma coluna diferente e aplicar a fórmula countif para verificar a ocorrência das datas dos feriados na folha de dados.

Por exemplo, temos algumas datas mencionadas na coluna D e uma lista de feriados na coluna G. Através da formatação condicional, podemos destacar os feriados em VERMELHO na coluna D, se houver, comparando-os com as datas dos feriados mencionados na coluna G usando as etapas mencionadas abaixo:

• Abra a caixa de diálogo Nova regra e escolha Usar uma fórmula para determinar quais células formatar < /span>Selecionar tipo de regra.em
• Insira a fórmula =CONT.SE($G$3:$G$5,$D3)
• Clique em Formatar > Preencha e selecione a cor VERMELHO na paleta e clique em OK< /span>.

O countif() verifica a ocorrência das datas mencionadas na célula G3 – G5 com as datas mencionadas na coluna D começando na célula D3 .

5]Formatação condicional de datas com base em múltiplas condições

Vamos supor uma planilha onde temos reclamações levantadas sobre vários utensílios domésticos. A folha apresenta as datas em que as reclamações foram levantadas e as datas de vencimento e encerramento, conforme aplicável.

Utilizando formatação condicional, vamos marcar as reclamações em BRANCO para aquelas que foram FECHADAS, VERMELHA para aquelas que já ultrapassaram o prazo, mas ainda não foram encerradas, e AZUL para aquelas que ainda atingiram o prazo.

O processo envolveria a criação de duas regras de formatação condicional para os mesmos dados, uma para aqueles que ainda não atingiram o prazo e outra para aqueles que já ultrapassaram o prazo. Para fazer isso,

• Abra a janela Nova regra de formatação condicional e selecione Usar uma fórmula para determinar quais células formatar em Selecione um tipo de regra.
• Insira a fórmula mencionada abaixo

=AND($I10="",$H10>$K$6)

• Clique em Formatar, selecione a cor AZUL na paleta e clique em OK.
• Crie outra Nova Regra com o mesmo tipo de regra e insira a fórmula mencionada abaixo

=AND($I10="",$H10<$K$6)

• Clique em Formatar, selecione a cor VERMELHA na paleta e clique em OK.

Nesta fórmula, primeiro verificamos se a coluna Data de encerramento está em BRANCO ou não para determinar se a reclamação ainda está aberta E depois verificamos se a DATA DE VENCIMENTO for maior que a data atual ou menor para determinar se a data de vencimento está se aproximando ($H10>$K$6) ou ultrapassada ($ H10<$K$6). A data na célula K6 representa a data atual.

6] Destacando as próximas datas e atrasos

Para entender como podemos destacar atrasos em relação às datas em uma planilha Excel, consideremos um exemplo onde temos uma lista de ações com seus nomes e datas de vencimento. Através da formatação condicional, se precisarmos marcar os que estão prestes a expirar em VERDE e os que já expiraram em VERMELHO,

• Selecione o intervalo de dados e abra a caixa de diálogo Nova Regra.
• Escolha Formatar somente células que contenham em Selecionar tipo de regra
• Em Editar descrição da regra, escolha Valor da célula, Maior que, =Agora()+30 nos respectivos menus suspensos.
• Clique em Formatar, selecione VERDE na paleta de cores, e clique em OK.

• Novamente, abra uma Nova regra e escolha o mesmo Tipo de regra, < a i=4>como acima.
• Em Editar descrição da regra, escolha Valor da célula, Menos que, =Agora() nos respectivos menus suspensos.
• Clique em Formatar e selecione VERMELHO na cor palete e clique em OK.

Conclusão

Este tutorial tem como objetivo cobrir de forma abrangente as opções e funções que podem ser usadas para aplicar a Formatação Condicional em planilhas do Excel com base em datas. Pode ajudar a compreender como aplicar regras de formatação para aprimorar a visualização e análise de dados. Dominar a formatação condicional também pode ajudar a identificar tendências, verificar prazos e definir prioridades de tarefas, ajudando a tomar decisões informadas.

Como crio formatação condicional no Excel com base em datas?

Primeiro, selecione suas células de data para criar formatação condicional no Excel com base em datas. Clique em ‘Página inicial’ > ‘Formatação Condicional’ > ‘Nova regra’. Escolha ‘Formatar células que contêm’, selecione ‘Datas de ocorrência’ na Regra, escolha os critérios de data específicos e defina seu formato preferido clicando em ‘Formatar’. Inscreva-se clicando em ‘OK’. Isso destaca rapidamente as datas importantes em seus dados.

Como faço para formatar datas de vencimento condicionalmente no Excel?

Primeiro, selecione suas células com datas para formatar condicionalmente as datas de vencimento no Excel. Em seguida, clique em ‘Formatação Condicional’ na guia ‘Página inicial’. Escolha ‘Nova regra’, selecione ‘Formatar apenas células que contenham’, defina a regra como ‘Valor da célula’ e ‘menor que’ e insira sua data de vencimento ou uma fórmula. Clique em ‘Formatar’, escolha seu estilo de formatação e pressione ‘OK’ para aplicar.