Consejo: Otros idiomas son traducidos por Google. Puedes visitar el English versión de este enlace.
Iniciar sesión
x
or
x
x
Suscríbete
x

or

¿Cómo verificar si una fecha es día festivo y contar días excepto vacaciones en Excel?

Por ejemplo, si tiene una lista de fechas y desea verificar si existen días festivos en la lista, ¿cómo podría hacerlo rápidamente? En este artículo, le mostraré las formas de calcular todas las fechas de los días festivos estadounidenses dentro de determinados años, y luego verificará fácilmente si una fecha es un día festivo estadounidense en Excel.

Parte 1: Calcule festividades dentro de determinados años en Excel

Parte 2: compruebe si una fecha es feriado en Excel

Parte 3: Cuente los días entre dos días, excepto fines de semana y días festivos en Excel

Copie fórmulas exactamente / estáticamente sin cambiar referencias de celda en Excel

Kutools for Excel Copia exacta la utilidad puede ayudarlo a copiar fácilmente varias fórmulas exactamente sin cambiar las referencias de celda en Excel, lo que evita que las referencias de celda relativas se actualicen automáticamente. Función completa de prueba gratuita 60-day!

fórmulas de copia exactas 3

Ficha Office Habilite la edición y navegación con pestañas en Office y haga su trabajo mucho más fácil ...
Kutools for Excel trae las características avanzadas de 300 a Excel y aumenta su productividad en 80%
  • Super Formula Bar (edite fácilmente varias líneas de texto y fórmula); Diseño de lectura (lee y edita fácilmente un gran número de celdas); Pegar en rango filtrado...
  • Combinar celdas / filas / columnas y mantener datos; Contenido de celdas divididas; Combinar filas duplicadas y suma / promedio... Prevenir células duplicadas; Comparar rangos...
  • Seleccione Duplicado o Único Filas; Seleccionar filas en blanco (todas las celdas están vacías); Super Find y Fuzzy Find en muchos libros de trabajo; Selección aleatoria ...
  • Copia exacta Celdas múltiples sin cambiar la referencia de fórmula; Crear referencias automáticamente a múltiples hojas; Insertar viñetas, Casillas de verificación y más ...
  • Fórmulas favoritas e insertadas rápidamente, Gamas, cuadros y cuadros; Cifrar celdas con contraseña Crear una lista de correo y enviar correos electrónicos ...
  • Extracto del texto, Agregar texto, Eliminar por posición, Eliminar espacio; Crear e imprimir subtotales de paginación; Convertir entre contenido de celdas y comentarios...
  • Súper filtro (guardar y aplicar esquemas de filtro a otras hojas); Clasificación avanzada por mes / semana / día, frecuencia y más; Filtro especial por negrita, cursiva ...
  • Combinar libros de trabajo y hojas de trabajo; Combinar tablas basadas en columnas clave; Dividir datos en varias hojas; Conversión por lotes xls, xlsx y PDF...
  • Kutools funciona con Office 2007-2019 y 365. Es compatible con todos los idiomas y es muy fácil de instalar o implementar. Funciones completas de prueba gratuita de 60-day.

flecha azul burbuja derechaParte 1: Calcular días festivos con un año determinado en Excel

Antes de verificar si una fecha es un día festivo, debe enumerar todos los días festivos dentro de determinados años en Excel. Por lo tanto, preparar una mesa como se muestra en la siguiente pantalla hará que su trabajo sea más fácil.

doc comprobar si festivo 1

Hay tres tipos de días festivos en los Estados Unidos:

(1) El primer tipo es un día festivo en una fecha fija, como el Día de Año Nuevo en Jan 1st. podemos calcular fácilmente el día de Año Nuevo con la fórmula = FECHA (año dado, 1,1);

(2) El segundo tipo es un día festivo en un día laborable fijo, como el Día del Presidente. Podemos calcular fácilmente el día del Presidente con la fórmula = DATE (año dado, 1,1) + 14 + ELEGIR (WEEKDAY (DATE (año dado, 1,1)), 1,0,6,5,4,3,2);

(3) Y el último tipo es el Día de los Caídos, podemos calcular fácilmente el Día de los Caídos con la fórmula = FECHA (año dado, 6,1) -EL DÍA (FECHA (año dado, 6,6)).

Aquí enumero fórmulas para calcular todos los días festivos en la siguiente tabla. Simplemente ingrese las fórmulas en la celda correcta y presione entrar clave uno por uno.

Vacaciones Celda Fórmulas
Día de Año Nuevo C2 = FECHA (C1,1,1)
Día de Martin Luther King Jr. C3 = DATE (C1,1,1) + 14 + ELEGIR (WEEKDAY (DATE (C1,1,1)), 1,0,6,5,4,3,2)
Día del Presidente C4 = DATE (C1,2,1) + 14 + ELEGIR (WEEKDAY (DATE (C1,2,1)), 1,0,6,5,4,3,2)
Día de la Recordación (Memorial Day) C5 = DATE (C1,6,1) -WEEKDAY (DATE (C1,6,6))
Día de la Independencia C6 = FECHA (C1,7,4)
Día del Trabajo C7 = DATE (C1,9,1) + ELEGIR (WEEKDAY (DATE (C1,9,1)), 1,0,6,5,4,3,2)
Día de la Raza C8 = DATE (C1,10,1) + 7 + ELEGIR (WEEKDAY (DATE (C1,10,1)), 1,0,6,5,4,3,2)
Día de los Veteranos C9 = FECHA (C1,11,11)
Día de Acción de Gracias C10 = DATE (C1,11,1) + 21 + ELEGIR (WEEKDAY (DATE (C1,11,1)), 4,3,2,1,0,6,5)
Día de Navidad C11 = FECHA (C1,12,25)

Nota: En las fórmulas de la tabla anterior, C1 es la celda de referencia que ubica el año determinado. En nuestro ejemplo, significa Year 2015, y puede cambiarlo en función de sus necesidades.

Con estas fórmulas, puede calcular fácilmente las fechas de los días festivos con determinados años. Vea la captura de pantalla a continuación:

doc comprobar si festivo 2


Guarde un rango como entrada de Autotexto (formatos de celda y fórmulas restantes) para reutilizar en el futuro

Debe ser muy tedioso referir celdas y aplicar fórmulas para calcular cada día festivo. Kutools for Excel proporciona una linda solución de Texto automático utilidad para guardar el rango como una entrada de Autotexto, que puede seguir siendo los formatos de celda y las fórmulas en el rango. Y luego volverá a usar este rango con un solo clic. ¡El trabajo se vuelve fácil con solo un clic para insertar esta tabla y cambiar el año en esta tabla! Función completa de prueba gratuita 60-day!

ad auto american holidays 1

flecha azul burbuja derechaParte 2: compruebe si una fecha es feriado en Excel

Después de enumerar todas las fechas de días festivos de años específicos, podemos verificar fácilmente si una fecha es festivo o no con fórmulas en Excel. Supongamos que tiene una lista de fechas como la siguiente captura de pantalla que se muestra, y le presentaré las formas de hacerlo fácilmente.

Seleccione una celda en blanco además de la lista de fechas, dice Cell B18, ingrese la fórmula = IF (CONTINGENTE ($ C $ 2: $ D $ 11, A18), "Vacaciones", "No") en él, y luego arrastre el Mango de relleno al rango que necesita. Ver captura de pantalla arriba:

Notas:

(1) En la fórmula = IF (COUNTIF ($ C $ 2: $ D $ 11, A18), "Holiday", "No"), $ C $ 2: $ D $ 11 es el rango de días festivos en años específicos, y A18 es la celda con la fecha que desea verificar si se trata de un día festivo, y puede cambiarlos en función de sus necesidades. Y esta fórmula devolverá "Vacaciones" si la fecha específica es un día festivo, y devolverá "No" si no es así.

(2) También puede aplicar esta fórmula de matriz = IF (O ($ C $ 2: $ D $ 11 = A18), "Vacaciones", "NO") para verificar si la fecha correspondiente es feriado o no.


flecha azul burbuja derecha Parte 3: Cuente los días entre dos días, excepto fines de semana y días festivos en Excel

En la Parte 1 hemos enumerado todas las vacaciones en un año determinado, y ahora este método lo guiará a contar la cantidad de días, excepto todos los fines de semana y días festivos en un rango de fechas.

Seleccione una celda en blanco; devolverá la cantidad de días e ingrese la fórmula = DÍAS DE LA RED (E1, E2, B2: B10) en él, y presione el entrar clave.

Nota: En las celdas anteriores, E1 es la fecha de inicio del rango de fechas especificado, E2 es la fecha de finalización y B2: B10 es la lista de vacaciones que calculamos en la Parte 1.

doc comprobar si festivo 6

Ahora obtendrá el número de días, excepto fines de semana y días festivos en el rango de fechas especificado.


Kutools for Excel: la mejor herramienta de productividad de Office aumenta su productividad en un 80%

  • Super Formula Bar (edite fácilmente varias líneas de texto y fórmula); Diseño de lectura (lee y edita fácilmente un gran número de celdas); Pegar en rango filtrado...
  • Combinar celdas / filas / columnas y mantener datos; Contenido de celdas divididas; Combinar filas duplicadas y suma / promedio... Prevenir células duplicadas; Comparar rangos...
  • Seleccione Duplicado o Único Filas; Seleccionar filas en blanco (todas las celdas están vacías); Super Find y Fuzzy Find en muchos libros de trabajo; Selección aleatoria ...
  • Copia exacta Celdas múltiples sin cambiar la referencia de fórmula; Crear referencias automáticamente a múltiples hojas; Insertar viñetas, Casillas de verificación y más ...
  • Fórmulas favoritas e insertadas rápidamente, Gamas, cuadros y cuadros; Cifrar celdas con contraseña Crear una lista de correo y enviar correos electrónicos ...
  • Extracto del texto, Agregar texto, Eliminar por posición, Eliminar espacio; Crear e imprimir subtotales de paginación; Convertir entre contenido de celdas y comentarios...
  • Súper filtro (guardar y aplicar esquemas de filtro a otras hojas); Clasificación avanzada por mes / semana / día, frecuencia y más; Filtro especial por negrita, cursiva ...
  • Combinar libros de trabajo y hojas de trabajo; Combinar tablas basadas en columnas clave; Dividir datos en varias hojas; Conversión por lotes xls, xlsx y PDF...
  • Funciona con Office 2007-2019 y 365, y es compatible con todos los idiomas. Es fácil de implementar en su empresa. Funciones completas de prueba gratuita de 60-day.
pestaña kte 201905

Office Tab lleva la interfaz con pestañas a Office y hace que su trabajo sea mucho más fácil

  • Habilitar la edición y lectura con pestañas en Word, Excel, PowerPoint, Editor, Acceso, Visio y Proyecto.
  • Abra y cree varios documentos en nuevas pestañas de la misma ventana, en lugar de en nuevas ventanas.
  • ¡Aumenta tu productividad en un 50% y reduce cientos de clics de ratón por ti todos los días!
fondo officetab
Say something here...
symbols left.
You are guest ( Sign Up? )
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.
    KC · 11 months 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 · 8 months 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 · 2 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 · 2 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 · 2 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 · 10 months 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 · 3 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)