Como verificar se uma data é feriado e contar os dias, exceto feriados em Excel?
Por exemplo, você tem uma lista de datas e deseja verificar se existe algum feriado na lista, como poderia fazer isso rapidamente? Neste artigo, mostrarei maneiras de calcular todas as datas de feriados americanos em determinados anos e, em seguida, verificar se uma data é feriado americano em Excel facilmente.
- Parte 1: Calcular feriados em determinados anos em Excel
- Parte 2: Verifique se uma data é feriado em Excel
- Parte 3: Contar os dias entre dois dias, exceto fins de semana e feriados em Excel
Parte 1: Calcular feriados com determinado ano em Excel
Antes de verificar se uma data é feriado, você deve listar todos os feriados de um determinado ano em Excel. Portanto, preparar uma tabela conforme a captura de tela mostrada a seguir tornará seu trabalho mais fácil.
Existem três tipos de feriados americanos:
(1) O primeiro tipo é um feriado em uma data fixa, como o dia de Ano Novo em 1º de janeiro. podemos calcular facilmente o dia de ano novo com a fórmula = DATA (dado ano, 1,1);
(2) O segundo tipo é um feriado em um dia da semana fixo, como o Dia do Presidente. Podemos calcular facilmente o dia do presidente com a fórmula = DATA (Dado Ano, 1,1) + 14 + ESCOLHER (DIA DA SEMANA (DATA (Dado Ano, 1,1)), 1,0,6,5,4,3,2);
(3) E o último tipo é o Memorial Day, podemos calcular facilmente o Memorial Day com a fórmula = DATA (dado ano, 6,1) -WEEKDAY (DATA (dado ano, 6,6)).
Aqui, listo as fórmulas para calcular todos os feriados na tabela a seguir. Basta inserir as fórmulas na célula adequada e pressionar Entrar chave um por um.
| Feriado | Célula | Fórmulas |
| Dia de Ano Novo | C2 | = DATA (C1,1,1) |
| Dia de Martin Luther King Jr. | C3 | = DATA (C1,1,1) + 14 + ESCOLHER (DIA DA SEMANA (DATA (C1,1,1)), 1,0,6,5,4,3,2) |
| Dia do Presidente | C4 | = DATA (C1,2,1) + 14 + ESCOLHER (DIA DA SEMANA (DATA (C1,2,1)), 1,0,6,5,4,3,2) |
| Memorial Day | C5 | = DATA (C1,6,1) -SEMANA (DATA (C1,6,6)) |
| Dia da Independência | C6 | = DATA (C1,7,4) |
| Dia do Trabalho | C7 | = DATA (C1,9,1) + ESCOLHER (DIA DA SEMANA (DATA (C1,9,1)), 1,0,6,5,4,3,2) |
| Dia de Colombo | C8 | = DATA (C1,10,1) + 7 + ESCOLHER (DIA DA SEMANA (DATA (C1,10,1)), 1,0,6,5,4,3,2) |
| Dia dos Veteranos | C9 | = DATA (C1,11,11) |
| Thanksgiving Day | C10 | = DATA (C1,11,1) + 21 + ESCOLHER (DIA DA SEMANA (DATA (C1,11,1)), 4,3,2,1,0,6,5) |
| Dia de Natal | C11 | = DATA (C1,12,25) |
Nota: Nas fórmulas da tabela acima, o C1 é a célula de referência que localiza o ano dado. Em nosso exemplo, significa Ano de 2015 e você pode alterá-lo com base em suas necessidades.
Com essas fórmulas, você pode calcular facilmente as datas dos feriados com determinados anos. Veja a captura de tela abaixo:
Salve um intervalo como entrada de AutoTexto (formatos de células e fórmulas restantes) para reutilizar no futuro
Deve ser muito tedioso referenciar células e aplicar fórmulas para calcular cada feriado. Kutools for Excel fornece uma solução alternativa atraente de Auto texto utilitário para salvar o intervalo como uma entrada de AutoTexto, que pode permanecer os formatos de células e fórmulas no intervalo. E então você reutilizará esse intervalo com apenas um clique. O trabalho fica fácil com apenas um clique para inserir esta tabela e alterar o ano nesta tabela!
Kutools for Excel - Inclui mais de 300 ferramentas úteis para Excel. Avaliação gratuita completa de recursos 30-dia, sem necessidade de cartão de crédito! Get It Now
Parte 2: Verifique se uma data é feriado em Excel
Depois de listar todas as datas de feriados de anos específicos, podemos facilmente verificar se uma data é feriado ou não com fórmulas em Excel. Supondo que você tenha uma lista de datas conforme a captura de tela mostrada a seguir, apresentarei as maneiras de fazer isso facilmente.
Selecione uma célula em branco ao lado da lista de datas, diz Célula B18, insira a fórmula = IF (COUNTIF ($ C $ 2: $ D $ 11, A18), "Feriado", "Não") nele e arraste a alça de preenchimento para o intervalo que você precisa. Veja a imagem acima:
Notas:
(1) Na fórmula = IF (COUNTIF ($ C $ 2: $ D $ 11, A18), "Feriado", "Não"), $ C $ 2: $ D $ 11 é o intervalo de feriados em anos específicos, e A18 é a célula com a data que você deseja verificar se é feriado, e você pode alterá-la de acordo com suas necessidades. E essa fórmula retornará "Feriado" se a data específica for um feriado público e retornará "Não" se não for.
(2) Você também pode aplicar esta fórmula de matriz = SE (OU ($ C $ 2: $ D $ 11 = A18), "Feriado", "NÃO") para verificar se a data correspondente é feriado ou não.
Parte 3: Contar os dias entre dois dias, exceto fins de semana e feriados em Excel
Na Parte 1, listamos todos os feriados em um determinado ano e agora este método o guiará para contar o número de dias, exceto todos os fins de semana e feriados em um intervalo de datas.
Selecione uma célula em branco, você retornará o número de dias e insira a fórmula = DIAS DE TRABALHO (E1, E2, B2: B10) nele, e pressione o Entrar chave.
Anote os: Nas células acima, E1 é a data de início do intervalo de datas especificado, E2 é a data de término e B2: B10 é a lista de feriados que calculamos na Parte 1.
Agora você obterá o número de dias, exceto fins de semana e feriados no intervalo de datas especificado.
Copie fórmulas exatamente/estaticamente sem alterar as referências de células em Excel
Kutools for Excel Cópia exata utilitário pode ajudá-lo a copiar facilmente várias fórmulas exatamente sem alterar as referências de células em Excel, evitando que referências de células relativas sejam atualizadas automaticamente.
Kutools for Excel - Inclui mais de 300 ferramentas úteis para Excel. Avaliação gratuita completa de recursos 30-dia, sem necessidade de cartão de crédito! Get It Now
Melhores ferramentas de produtividade de escritório
Turbine suas planilhas: Experimente eficiência como nunca antes com Kutools for Excel
Kutools for Excel possui mais de 300 recursos, garantindo que o que você precisa está a apenas um clique de distância...
Suporta Escritório/Excel 2007-2021 e mais recentes, incluindo 365 | Disponível em 44 idiomas | Desfrute de um teste gratuito de 30 dias com todos os recursos.
Office Tab Traz a interface com guias para o Office e torna seu trabalho muito mais fácil
- Ative a edição e leitura com guias em Word, Excel, Power Point, Publisher, Access, Visio e Project.
- Abra e crie vários documentos em novas guias da mesma janela, em vez de em novas janelas.
- Aumenta sua produtividade em 50% e reduz centenas de cliques do mouse para você todos os dias!
