martes, 12 de noviembre de 2013

Contando días laborables.. incluídos sábados.

Trabajaremos hoy un poco combinando algunas funciones matriciales con otras funciones de Fechas como DIASEM y DIAS.LAB que ya conocemos (ver), y aprenderemos una nueva que aparece ne versiones 2010+ que es DIAS.LAB.INTL.
El post de hoy sirve en respuesta a una consulta recibida de un usuario ya avanzado:

...necesito saber los días laborables,incluyendo sabados entre dos fechas, pero descontando los días festivos.
ejemplo; del 01/11/203 al 30/11/2013 saber cuantos dias laborables ( de lunes a sabado) hay pero descontando los festivos de ese mes.

he probado con =SUMA(SI(DIASEM(A12+FILA(INDIRECTO("1:"&B12-A12-1));2)<>7;1;0);1), donde A12 y B12 son 1/11/13 y 30/11/13 y no me cuenta el dia 1/11/13 pero tampoco el 9/11/13 que es festivo.
Tengo un rango D2:D6 con los feriados...


Siguiendo las indicaciones dadas replicamos el ejercicio, he anexado además el detalle del mes de estudio para verificar y ver mejor el planteamiento:

Contando días laborables.. incluídos sábados.


Como observamos el planteamiento es claro, necesitamos contar los dias del mes de Lunes a Sábado que no sean festivos. Para el mes de Noviembre con festivos el 01/11/2013 y 9/11/2013 nos saldrían 24 días laborables.

No podemos emplear directamente la función DIAS.LAB ya que esta función no tiene en cuenta los sábados y domingos como laborables; pero en cambio en versiones Excel 2010 + podemos emplear la función
=DIAS.LAB.INTL(fecha_inicial; fecha_final; [fin_de_semana]; [festivos])
estando la clave en el tercer argumento, que nos permite indicar qué entendemos como fin de semana:
Número de fin de semana /Días de fin de semana
1 u omitido := Sábado, Domingo
2 := Domingo, Lunes
3 := Lunes, Martes
4 := Martes, Miércoles
5 := Miércoles, Jueves
6 := Jueves, Viernes
7 := Viernes, Sábado
11 := Solo domingo
12 := Solo sábado
13 := Solo martes
14 := Solo miércoles
15 := Solo jueves
16 := Solo viernes
17 := Solo sábado

en nuestro caso aplicaremos el valor 11, quedando nuestra fórmula para el ejemplo planteado:
=DIAS.LAB.INTL(A12;B12;11;D2:D6)
lo que nos devuelve efectivamente el valor buscado de 24 días no festivos en el mes.

Contando días laborables.. incluídos sábados.



El problema viene cuando trabajamos con versiones anteriores de Excel, es entonces cuando no queda más remedio que emplear matriciales, combinadas con otras funciones.
Desglosaré la fórmula para verla mejor. En primer lugar calculo el número de días laborables con DIAS.LAB sin incluir ni sábados ni domingos en la celda D9:
=DIAS.LAB(A12;B12;D2:D6)
lo que devuelve 20 días, esto son todos los lunes a viernes del mes excepto el 01/11/2013 que es viernes festivo.

Por otro lado en la celda D12 añadimos la fórmula matricial
=SUMA(SI(DIASEM(A12+FILA(INDIRECTO("1:"&B12-A12-1));2)=6;1;0);1)
que construye un rango 'virtual' con todos los días del mes, a los que aplica una condición de si 'es sábado' (DIASEM = 6) entonces valor 1 en otro caso 0, tras sumar el resultado matricial obtenemos el número de sábados total del mes (incluidos festivos)

A este último valor de sábados totales solo nos queda restar los festivos que sean sábado, por lo que aplicaremos enla celda F11 una nueva fórmula matricial aplicada sobre nuestro rango de Festivos D2:D6:
=SUMA(SI(SI(DIASEM(D2:D6;2)=6;D2:D6;0)>0;1;0))
lo que obviamente nos devuelve un resultado de 1 festivo que es sábado.

Contando días laborables.. incluídos sábados.


El resultado combinando estos cálculos lo vemos en la imagen anterior, el número de dias es de 24.

Una tercera forma sería utilizar parte de la matricial aportada por el lector que realiza la pregunta, al que habría que restar sencillamente el númer de festivos sábados calculado en el caso anterior, es decir, matricialmente:
=SUMA(SI(DIASEM(A12+FILA(INDIRECTO("1:"&B12-A12-1));2)<>7;1;0);1)-SUMA(SI(SI(DIASEM(D2:D6;2)=6;D2:D6;0)>0;1;0))


Seguro existe alguna otra forma.. que por supuesto, como siempre estoy abierto a escuchar.

13 comentarios:

  1. Hola excelforo,
    Una consulta sobre la fórmula matricial =SUMA(SI(DIASEM(A12+FILA(INDIRECTO("1:"&B12-A12-1));2)<>7;1;0);1)-SUMA(SI(SI(DIASEM(D2:D6;2)=6;D2:D6;0)>0;1;0))
    porqué no me resta un día más cuando pongo una fecha mas en la celdas D2:D6, por ejemplo 01/11/2013, 09/11/2013, 29/11/2013. Como resultado debería salir 23.
    espero a verme explicado.
    Atentamente
    Juan.

    ResponderEliminar
    Respuestas
    1. Hola Juan,
      correcto esa tercera forma que aportaba el lector que planteaba la cuestión sólo tiene en cuenta los días diferentes a Domingo.. NO los festivos, por lo que en efecto es incorrecta, falla al no considera el listado de Festivos...

      Puedes emplear las dos primeras que si están comprobadas y funcionan bien.
      Slds

      Eliminar
  2. Deseo calcular dias trabajados incluyendo sabados, domingos y festivos
    Pero ese calculo deseo a facha futura, ejemplo desde 09/11/2011 al 30/11/2013, intente con la función dias.lab, pero esta me excluye los dias descritos arriba.
    Ya que neesito validar si aplican o no a una dotación.

    ResponderEliminar
    Respuestas
    1. Hola Afrojam, un placer saludarte.
      las funciones DIAS.LAB O DIAS.LAB.INTL comentadas en este mismo post, están pensadas para trabajar bajo el supuesto de días festivos (opcional) y días no labroables (algún día en la semana)... si tu supuesto es calcular días sin estas restricciones, parece que lo único que necesitas saber es la diferencia de dias entre una fecha y otra, por lo que sería tan sencillo como restar una fecha menos otra.
      Si en A2 tienes 30/11/2013 y en A1 tienes 09/11/2011, en otra celda C1 tendrías:
      =A2-A1

      Espero te sirva.
      Un cordial saludo

      Eliminar
  3. Buenas tardes como se hace cuando no hablamos de un mes especifico si no que la fecha de inicio pertenece a un mes y la fecha final a otro mes, ademas el rango de los festivos incluye todos los festivos del año mas los sábados que yo quiera mostrar como festivos ya que mi fecha inicial es 22/09/15 y final es 31/10/15 los dias laborales estan bien me da 28, los sabados me da bien son 6 el problema esta en que en el rango de festivos he incluido 3 sabados de los cuales solo uno esta entre en rango de fecha inicial y final, yo nesecito que me enumere los sabados festivos pero entre las fecha que estipule y no todos los que esten en el rango de festivos. Espero me puedan ayudar Gracias.

    ResponderEliminar
    Respuestas
    1. Lo siento Ronald..
      no entendí el planteamiento
      Saludos

      Eliminar
  4. Buenos días,

    Si yo quiero saber la fecha final. Por ejemplo, si comienza hoy 13/06/2016 su cita y le doy una frecuenta de lunes - miercoles y viernes, quiero saber que dia termina su cita, como haria para calcular eso?

    ResponderEliminar
    Respuestas
    1. Hola Karla,
      si he entendido bien, lo que necesitas es obtener el siguiente día (lunes, miércoles o viernes) siguiente a la fecha dada (13/06/2016)??
      correcto?

      Eliminar
  5. Estimado Ismael:
    Cada lunes debo sacar un reporte que le llamamos "Comparativo", que contiene un consolidado entre igual número de días hábiles entre el mes en curso y el mes anterior. Yo estoy creando una macro en vba que me genere el consolidado, permitiendo calcular cuántos días hábiles se consideran para el mes en curso y el mes anterior; hasta ahí no hay problema por cuanto uso la función =DIAS.LAB(FECHA(AÑO(HOY());MES(HOY());DIA(1));FECHA(AÑO(HOY());MES(HOY());DIA(HOY()-1));feriados), que por ejemplo entre el 01 y el 09 de Junio de 2017 son 07 días hábiles; el asunto es que debo determinar las fechas de inicio y fin del mes pasado corridos 07 días hábiles, que en la práctica debieran ser entre el 02 y el 10 de Mayo. Mi consulta es como puedo utilizar el resultado de números de días hábiles del mes actual para determinar el inicio y fin del período del mes anterior; en el ejemplo 07 días, ya que si yo sumara entre el 01-May-17 + 7 daría 08 y no 10. Espero entiendas el fondo de la consulta y me puedas ayudar.
    Saludos cordiales:

    ResponderEliminar
    Respuestas
    1. Hola,
      emplea en tu fórmula la función FECHA.MES(fecha;-1)... eso te llevará al mes inmediato anterior y podrás contabilizar los días hábiles de ese mes-1
      Saludos

      Eliminar
  6. hola deseo realizar una tabla que refleje solo los fines de semana libres que tenga cada empleado no importa si se repiten. correo adriansotor76@gmail.com

    ResponderEliminar
    Respuestas
    1. Hola,
      puedes especificar algo más de la duda que tienes??..
      y si fuera posible exponer algo sobre la distribución de info sobre la hoja
      Slds

      Eliminar