Calendario control de vacaciones en Excel

Compatibilidad: Truco compatible con Excel 365Truco compatible con Excel 2016Truco compatible con Excel 2013Truco compatible con Excel 2010Truco compatible con Excel 2007

Deseamos crear un calendario control de vacaciones en Excel.

El calendario permite marcar los días no laborables, sábados, domingos, festivos, festivos locales, asuntos personales y vacaciones.

Ent437z
Aplicamos formato condicional que remarque los días de vacaciones o no trabajados en el calendario, mediante funciones y validación de datos obtenemos el resultado deseado.
  • REFERENCIA AÑO

  1. Clic sobre la celda AD2. Celda donde aplicaremos validación de datos tipo lista para seleccionar el año deseado.

  2. Ir a: Cinta de opciones / Pestaña: Datos / Grupo: Herramientas de datos / Validación de datos. Mostrará ventana de validación.

  3. Ir a: Permitir / Seleccionar: Lista. Tipo de validación deseado.

    Calendario control de vacaciones: Ventana de validación de datos, Lista para los años
  4. Ir a: Origen / Escribir: 2013; 2014; 2015; 2016. O los años deseados.

  5. Ir a: Celda AC2 / Escribir: "Año: ". Como rótulo del contenido.

  • REFERENCIA MES

  1. Clic sobre la celda AH2. Se selecciona para aplicar validación tipo lista.

  2. Ir a: Cinta de opciones / Pestaña: Datos / Grupo: Herramientas de datos / Validación de datos. Mostrará ventana de validación.

    Ventana de validación de datos: Lista para los meses
  3. Ir a: Permitir / Seleccionar: Lista. Tipo de validación deseado.

  4. Ir a: Origen / Escribir: Enero; Febrero; Marzo; Abril; Mayo; Junio; Julio; Agosto; Septiembre; Octubre; Noviembre; Diciembre.

  5. Ir a: Celda AG2 / Escribir: "Mes: ". Como rótulo del contenido.

  6. Ir a: celda B4 / Escribir:

    =SI(AH2="Enero";1;SI(AH2="Febrero";2; SI(AH2="Marzo";3; SI(AH2="Abril";4; SI(AH2="Mayo";5; SI(AH2="Junio";6; SI(AH2="Julio";7; SI(AH2="Agosto";8; SI(AH2="Septiembre";9; SI(AH2="Octubre";10; SI(AH2="Noviembre";11; SI(AH2="Diciembre";12; ""))))))))))))

    Función SI anidada para identificar el número de mes seleccionado.

  7. FORMATO DE CELDA. Aplicamos formato de celda para que muestre el mes personalizado.

  8. Clic sobre la celda B4 / Botón derecho del ratón / Formato de celdas.

    Ventana formato de celda, Personalizado ''Mes'' ##
  9. Ir a: Pestaña: Número / Categoría: Personalizada / Ir a: Tipo / Escribir: "Mes: " ##. Mostrará el texto de mes junto al número seleccionado en AH2, para nuestro calendario control de vacaciones.

  • REFERENCIA DÍA

  1. Ir a: Celda C5 / Escribir: 1. Como el primer día del mes.

  2. APLICAR SERIE. Clic sobre la celda C5 / Arrastrar el punto de relleno (inferior derecha) hasta la celda AG5.

  3. Clic sobre el botón de opciones / Serie de relleno.

    Botón de opciones: Selección de relleno para los días
  4. Ir a: Celda C4 / Escribir:

    =TEXTO(DIASEM(FECHA($AD$2;$B$4;C5);1);"ddd")
  5. Copiar la celda C4 / Pegar en el rango D4:AG4.

  • TABLA

  1. Ir a: Cinta de Opciones / Pestaña: Insertar / Grupo: Tabla / Tabla. Mostrará ventana de crear tabla.

    Ventana de crear tabla con rango y encabezados
  2. Seleccionar el rango: B5:AG9.

  3. Activar la casilla: La tabla tiene encabezados.

  4. Pulsar el botón Aceptar.

  5. OCULTAR FILTRO. Ir a: Cinta de Opciones / Pestaña: Datos / Grupo: Ordenar y filtrar / Filtro. Oculta el botón de filtro en los encabezados.

  6. Aplicar las opciones de formato deseadas.

  7. FILA TOTALES. Ir a: Cinta de Opciones / Pestaña: Diseño (Herramientas de tabla) / Grupo: Opciones de estilo de tabla / Activar la casilla: Fila de totales.

  8. Ir a: Rango C10:AG10 / Seleccionar la función CUENTA. Para que muestre el total de la columna en nuestro calendario control de vacaciones.

  • LEYENDA

  1. Ir a: Celda C12 / Escribir: F. Para indicar los días festivos.

  2. Ir a: Celda C13 / Escribir: L. Para indicar los días festivos locales.

  3. Ir a: Celda C14 / Escribir: P. Para indicar los días personales.

  4. Ir a: Celda C15 / Escribir: V. Para indicar los días de vacaciones.

  5. Aplicar color de relleno que coincida con el color del formato condicional.

Rango C12:C15 con letra y relleno que identifica el valor en el calendario
  • FORMATO CONDICIONAL: SÁBADOS

  1. Seleccionar el rango: C6:AG9.

  2. Ir a: Cinta de opciones / Pestaña: Inicio / Grupo: Estilos / Formato condicional / Nueva Regla. Mostrará ventana de nueva regla de formato.

  3. Pulsar sobre: Utilice una fórmula que determine las celdas para aplicar formato. Mostrará la descripción de la regla.

    Ventana de nueva regla de formato con fórmula =C$4="sáb"
  4. Ir a: Dar formato a los valores donde… / Escribir: =C$4="sáb"

  5. Pulsar el botón Formato / Aplicar el Color de relleno deseado. Para identificar los sábados en el calendario.

  • FORMATO CONDICIONAL: DOMINGOS

  1. Seleccionar el rango: C6:AG9.

  2. Ir a: Cinta de opciones / Pestaña: Inicio / Grupo: Estilos / Formato condicional / Nueva Regla. Mostrará ventana de nueva regla de formato.

  3. Pulsar sobre: Utilice una fórmula que determine las celdas para aplicar formato. Mostrará la descripción de la regla.

  4. Ir a: Dar formato a los valores donde… / Escribir: =C$4="dom"

  5. Pulsar el botón Formato / Aplicar el Color de relleno deseado. Para identificar los domingos en el calendario.

  • FORMATO CONDICIONAL: FESTIVOS

  1. Seleccionar el rango: C6:AG9.

  2. Ir a: Cinta de opciones / Pestaña: Inicio / Grupo: Estilos / Formato condicional / Nueva Regla. Mostrará ventana de nueva regla de formato.

  3. Pulsar sobre: Aplicar formato únicamente a las celdas que contengan. Mostrará la descripción de la regla.

    Ventana de nueva regla de formato con fórmula =$C$13
  4. Seleccionar: Valor de la celda / Igual a / Escribir: =$C$12

  5. Pulsar el botón Formato / Aplicar el Color de relleno deseado. Para identificar los días festivos en el calendario.

  • FORMATO CONDICIONAL: LOCALES

  1. Seleccionar el rango: C6:AG9.

  2. Ir a: Cinta de opciones / Pestaña: Inicio / Grupo: Estilos / Formato condicional / Nueva Regla. Mostrará ventana de nueva regla de formato.

  3. Pulsar sobre: Aplicar formato únicamente a las celdas que contengan. Mostrará la descripción de la regla.

  4. Seleccionar: Valor de la celda / Igual a / Escribir: =$C$13

  5. Pulsar el botón Formato / Aplicar el Color de relleno deseado. Para identificar los días festivos locales en el calendario.

  • FORMATO CONDICIONAL: PERSONALES

  1. Seleccionar el rango: C6:AG9.

  2. Ir a: Cinta de opciones / Pestaña: Inicio / Grupo: Estilos / Formato condicional / Nueva Regla. Mostrará ventana de nueva regla de formato.

  3. Pulsar sobre: Aplicar formato únicamente a las celdas que contengan. Mostrará la descripción de la regla.

  4. Seleccionar: Valor de la celda / Igual a / Escribir: =$C$14

  5. Pulsar el botón Formato / Aplicar el Color de relleno deseado. Para identificar los días personales.

  • FORMATO CONDICIONAL: VACACIONES

  1. Seleccionar el rango: C6:AG9.

  2. Ir a: Cinta de opciones / Pestaña: Inicio / Grupo: Estilos / Formato condicional / Nueva Regla. Mostrará ventana de nueva regla de formato.

  3. Pulsar sobre: Aplicar formato únicamente a las celdas que contengan. Mostrará la descripción de la regla.

  4. Seleccionar: Valor de la celda / Igual a / Escribir: =$C$15

  5. Pulsar el botón Formato / Aplicar el Color de relleno deseado. Para identificar los días de vacaciones en el calendario.

  • TRABAJADORES

  1. Ir a: Celda B5 / Escribir: Nombre. Como el rótulo de la columna.

  2. Ir a: Rango B6:B9 / Escribir el nombre de los trabajadores.

Rango B6:B9 con el nombre del trabajador
  • TOTAL DÍAS TRABAJADOR

  1. Ir a: Celda AH5 / Escribir: Días Trabajador. Como el rótulo de la columna.

  2. Ir a: Celda AH6 / Escribir: =CONTARA(C6:AG6) La fórmula se convertirá en =CONTARA(Tabla1[@[1]:[31]]) al ser una tabla.

  3. Copiar la celda AH6.

  4. Pegar en el rango AH7:AH9. Para todos los trabajadores

Celda AH6 con fórmula =CONTARA(Tabla1[@[1]:[31]])
  1. Seleccionar el año deseado.

  2. Seleccionar el mes deseado.

  3. Ir al trabajador deseado / Identificar el día / Escribir la letra de la leyenda. Aplicará el color según la leyenda y la validación de datos.

Hoja de Excel añadiendo días al calendario según la leyenda

Pudiendo crear en nuestra hoja de Excel un calendario control de vacaciones en Excel para los trabajadores de nuestra empresa.

Nivel de dificultad: Avanzado Excel Avanzado

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *