Note: The other languages of the website are Google-translated. Back to English
Registo de cliente  \/ 
x
or
x
Registe-se  \/ 
x

or

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

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)

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:

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 referir células e aplicar fórmulas para calcular cada feriado. Kutools para Excel oferece uma solução 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! Avaliação gratuita de recursos completos por 30 dias!
feriados americanos automotivos 1

Kutools for Excel - Inclui mais de 300 ferramentas úteis para Excel. Teste gratuito de 30 dias com recursos completos, 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.

Importante: 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 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. Avaliação gratuita de recursos completos por 30 dias!
fórmulas de cópia exata do anúncio 3

Kutools for Excel - Inclui mais de 300 ferramentas úteis para Excel. Teste gratuito de 30 dias com recursos completos, sem necessidade de cartão de crédito! Get It Now

As melhores ferramentas de produtividade para escritório

O Kutools for Excel resolve a maioria dos seus problemas e aumenta sua produtividade em 80%

  • armadilha para peixes: Insira rapidamente fórmulas complexas, gráficos e qualquer coisa que você tenha usado antes; Criptografar células com senha; Criar lista de discussão e enviar emails ...
  • Barra Super Fórmula (edite facilmente várias linhas de texto e fórmula); Layout de leitura (ler e editar facilmente um grande número de células); Colar na faixa filtrada...
  • Mesclar células / linhas / colunas sem perder dados; Dividir o conteúdo das células; Combinar linhas / colunas duplicadas... Evite células duplicadas; Comparar intervalos...
  • Selecione Duplicado ou Único Linhas; Selecione linhas em branco (todas as células estão vazias); Super Find e Fuzzy Find em muitos livros; Seleção aleatória ...
  • Cópia exata Várias células sem alterar a referência da fórmula; Criação automática de referências para várias folhas; Inserir marcadores, Caixas de seleção e mais ...
  • Extrair Texto, Adicionar texto, remover por posição, Remover Espaço; Criar e imprimir subtotais de paginação; Converter entre conteúdo de células e comentários...
  • Super Filtro (salvar e aplicar esquemas de filtro a outras planilhas); Classificação Avançada por mês / semana / dia, frequência e mais; Filtro Especial por negrito, itálico ...
  • Combine pastas de trabalho e planilhas; Mesclar tabelas com base em colunas-chave; Divida os dados em várias folhas; Conversão em lote de xls, xlsx e PDF...
  • Mais de 300 recursos poderosos. Suporta Office / Excel 2007-2019 e 365. Suporta todos os idiomas. Fácil implantação em sua empresa ou organização. Teste gratuito de 30 dias com recursos completos. Garantia de devolução do dinheiro em 60 dias.
guia kte 201905

Guia do Office 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!
parte inferior da aba do escritório
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    BethBZ · 2 years ago
    How would you calculate 15 calendar days from a given date including weekends but excluding holidays using a list/table of holiday dates?
  • To post as a guest, your comment is unpublished.
    KC · 3 years ago
    How to make it show the actual name of the holiday instead of just "holiday"?
    • To post as a guest, your comment is unpublished.
      kellytte · 3 years ago
      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.
  • To post as a guest, your comment is unpublished.
    Shawn Bull · 4 years ago
    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 :-)
  • To post as a guest, your comment is unpublished.
    Mark · 4 years ago
    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.
    • To post as a guest, your comment is unpublished.
      Tboo Ted Marshall · 4 years ago
      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.
      • To post as a guest, your comment is unpublished.
        Josh · 3 years ago
        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))
  • To post as a guest, your comment is unpublished.
    LINDA · 5 years ago
    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)