Como verificar se uma data é feriado e contar os dias exceto feriados no 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 as maneiras de calcular todas as datas de feriados americanos em determinados anos e, em seguida, verificar facilmente se uma data é um feriado americano no Excel.
- Parte 1: Calcule feriados em determinados anos no Excel
- Parte 2: Verifique se uma data é feriado no Excel
- Parte 3: conte os dias entre dois dias, exceto fins de semana e feriados no Excel
Parte 1: Calcular feriados com determinado ano no Excel
Antes de verificar se uma data é feriado, você deve listar todos os feriados em determinados anos no Excel. Portanto, preparar uma mesa 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) |
Observação: 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 consultar células e aplicar fórmulas para calcular cada feriado. Kutools for Excel oferece uma solução alternativa interessante para 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 - Turbine o Excel com mais de 300 ferramentas essenciais. Desfrute de um teste GRATUITO de 30 dias com todos os recursos, sem necessidade de cartão de crédito! Get It Now
Parte 2: Verifique se uma data é feriado no 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 no Excel. Supondo que você tenha uma lista de datas conforme a captura de tela 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: conte os dias entre dois dias, exceto fins de semana e feriados no 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.
Note: 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 no Excel
Kutools for Excel Cópia exata O utilitário pode ajudá-lo a copiar facilmente várias fórmulas exatamente sem alterar as referências de células no Excel, evitando que as referências de células relativas sejam atualizadas automaticamente.
Kutools for Excel - Turbine o Excel com mais de 300 ferramentas essenciais. Desfrute de um teste GRATUITO de 30 dias com todos os recursos, sem necessidade de cartão de crédito! Get It Now
Melhores ferramentas de produtividade de escritório
Aprimore suas habilidades de Excel com o Kutools para Excel e experimente uma eficiência como nunca antes. Kutools para Excel oferece mais de 300 recursos avançados para aumentar a produtividade e economizar tempo. Clique aqui para obter o recurso que você mais precisa...
Office Tab traz interface com guias para o Office e torna seu trabalho muito mais fácil
- Habilite a edição e leitura com guias em Word, Excel, PowerPoint, 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!
