Ir para o conteúdo principal

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 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.

doc verifique se feriado 1

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:

doc verifique se feriado 2

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!


feriados americanos automotivos 1

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.

doc verifique se feriado 6

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.


fórmulas de cópia exata do anúncio 3

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

Recursos mais comuns: Localizar/destacar/identificar duplicatas   |  Excluir linhas em branco   |  Combine colunas ou células sem perder dados   |   Rodada sem Fórmula ...
Super pesquisa: VLookup de múltiplos critérios    VLookup de múltiplos valores  |   VLookup em várias planilhas   |   Pesquisa Difusa ....
Lista suspensa avançada: Crie rapidamente uma lista suspensa   |  Lista suspensa de dependentes   |  Lista suspensa de seleção múltipla ....
Gerenciador de colunas: Adicione um número específico de colunas     Mover colunas   |   Exibir colunas   |   Comparar colunas com Selecionar células iguais e diferentes ...
Recursos em destaque: Foco da Grade   |  Vista de Design   |   Grande Barra de Fórmula    Gerenciador de pastas de trabalho e planilhas   |  Biblioteca (Auto texto)   |  Data Picker   |  Combinar planilhas   |  Criptografar/Descriptografar Células    Enviar e-mails por lista   |  Super Filtro   |   Filtro Especial (filtro negrito/itálico/tachado...) ...
15 principais conjuntos de ferramentas12 Texto Ferramentas (Adicionar texto, Remover Personagens, ...)   |   50+ de cores Tipos (Gráfico de Gantt, ...)   |   Mais de 40 práticos Fórmulas (Calcule a idade com base no aniversário, ...)   |   19 Inclusão Ferramentas (Insira o código QR, Inserir imagem do caminho, ...)   |   12 Conversão Ferramentas (Números para Words, Conversão de moedas, ...)   |   7 Unir e dividir Ferramentas (Combinar linhas avançadas, Dividir células, ...)   |   Muito mais...

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.

guia kte 201905


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!
Comments (8)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
How would you calculate 15 calendar days from a given date including weekends but excluding holidays using a list/table of holiday dates?
This comment was minimized by the moderator on the site
How to make it show the actual name of the holiday instead of just "holiday"?
This comment was minimized by the moderator on the site
Hi KC,
You can change the text “holiday” to INDEX($A$1:$A$11,MATCH(A18,$C$1:$C$11,0)) in the formula, and the whole formula will be changed to
=IF(COUNTIF($C$2:$C$11,A18),INDEX($A$1:$A$11,MATCH(A18,$C$1:$C$11,0)),"No")

Please note that the dates you will check should be placed in one column.
This comment was minimized by the moderator on the site
Thanks, I have worked out a system to determine whether a public holiday is a weekday, but this also gives an alternative. The problem is that if one does it per month, then there are gaps between days where public holidays occur during weekdays. An example as below taking part of December 2017. the figures to immediate right of dates (Col B) are the WEEKDAY values. If date falls on a Saturday or Sunday (value 6 or 7) then the C Column reflects a blank cell ("") if a weekday the Cell has a "1", if a Public Holiday during a weekday then a "0" 21/12/2017 4 1 22/12/2017 5 1 23/12/2017 6 24/12/2017 7 25/12/2017 1 0 26/12/2017 2 0 27/12/2017 3 1 28/12/2017 4 1 29/12/2017 5 1 30/12/2017 6 31/12/2017 7 I can then sort manually using the Filter approach to get the 1's in one continuous column of rows without the blanks or 0's. Copy and paste to a worksheet where I can import the data into the temperature charts. I am trying to get the filter section automated either via formula by deleting all the 0's and blank cells with the resultant shifting up of cells containing the 1's, or via VBA. The ultimate prize would be combining the steps in Column A and Column C into one formula. The end game is to populate a temperature chart with the workday name and in the next corresponding row the day of the required month Mon Tue Thu Fri 7 8 10 11 Using August as an example where the 9th is a public holiday that falls during a work day, resulting in the data relating to the Wed being removed and the rest of the column shifting up one (or more) places. Then transposed into the above cells. I hope I am explaining with sufficient clarity :-)
This comment was minimized by the moderator on the site
How could I make this work for Federal Holiday? Meaning if the date of a holiday happens to fall on a weekend then the Federal holiday would either be Friday or Monday.
This comment was minimized by the moderator on the site
I used the formulas above to calculate the actual day of the holiday and made a second column for Observed holiday. I made this formula to accomplish this: =IF((WEEKDAY(B15))=1,B15+1,IF((WEEKDAY(B15))=7,B15-1,B15)). The cell reference B15 is referring to the holiday which is in the actual holiday column, in this case New Years Day. When the actual holiday falls on a Saturday, the Observed holiday will be listed as Friday and for actual holidays falling on Sunday, the observed holiday will be listed as Monday. Hope this helps.
This comment was minimized by the moderator on the site
This is an accurate function which will work for New Years Day that would fall on a weekend (years 2022 and 2023): =WORKDAY(DATE(CalendarYear,1,1),--(WEEKDAY(DATE(CalendarYear,1,1),2)>5))
This comment was minimized by the moderator on the site
trying to make a formula for subtracting CALENDAR DAYS and holidays. I have been able to figure out for WORKDAYS and HOLIDAY, but I cannot figure out how to do CALENDAR days and holidays. here is what I am currently using for WORKDAYS AND HOLIDAYS. Help! So I need this to be CALENDAR days instead of WORKDAYS.] =WORKDAY(B28-5,1,HOLIDAYS)
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations