La función DIAS.LAB versión mejorada

lunes, junio 29, 2009

Excel viene provista con la función DIAS.LAB (NETWORKDAYS) que permite calcular la cantidad de días hábiles transcurridos entre dos fechas. En Excel 2007 esta función figura en la lista que muestra el asistente de funciones. En las versiones previas, hay que tener instalado el complemento Analysis Toolpak para que la función esté disponible.


funcion DIAS.LAB mejorada


Esta función permite tomar en cuenta las fechas de los feriados como argumento opcional, lo que la hace muy útil e “internacional”.

Pero también tiene una seria desventaja: en algunos países la semana de trabajo no comienza el lunes y no necesariamente los días de descanso son el sábado y el domingo. Por ejemplo en Egipto, Jordania Irak y Siria la semana laboral va de domingo a jueves y el fin de semana incluye el viernes y el sábado. En Israel la semana laboral corre de domingo a jueves o al mediodía del viernes. La función no permite cambiar estas definiciones lo que hace que en algunos países los resultados sean inexactos.

Lo mismo sucede si queremos calcular cuántos días tomará realizar un determinado proyecto cuando ciertos días de la semana no se realizan tareas relacionados con éste.

Empecemos por plantear el problema. Queremos saber cuántos días hábiles hay en el mes de abril del año 2009.


funcion DIAS.LAB mejorada


El resultado, que no toma en cuenta los feriados de abril, es 22 días. Para los lectores escépticos he agregado un calendario dinámico que muestra el mes de la fecha que aparece en la celda D2 (construido con la técnica que muestro en esta nota).

Tomemos como ejemplo España y agreguemos a nuestra fórmula los feriados del año 2009 (que aparecen en el rango G3:G14 de la hoja)


funcion DIAS.LAB mejorada
Vemos que el número de días hábiles es ahora 20 (el 12 de abril cae un domingo).

Nuestro problema empieza si vivimos en Jordania o Israel, o si en nuestro proyecto no se trabaja los martes y jueves, por ejemplo.

Chip Pearson propone dos soluciones a este problema usando fórmulas matriciales. Una fórmula que no toma en cuenta los feriados y una segunda que sí los incluye.

Tomemos el ejemplo anterior y supongamos que los días no laborales en nuestro proyecto son el martes y el jueves (días 2 y 4 de la semana respectivamente). La siguiente fórmula matricial permite calcular los días laborales, donde el sábado y el domingo lo son

=SUMA(SI(ESERROR(COINCIDIR(DIASEM(FILA(INDIRECTO(inicio&":"&fin)),2),dias_excluidos,0)),1,0))


funcion DIAS.LAB mejorada

Para que la fórmula sea más legible usamos los nombres

inicio = $C$2

fin = $C$3

días_excluidos = =DESREF(matricial!$F$4,0,0,CONTARA(matricial!$F$4:$F$35),1)

Para tomar en cuenta también los feriados debemos complicar un poco (bastante) la fórmula. Siguiendo con nuestro ejemplo, creamos un rango llamado “feriados” (B8:B19 en nuestro ejemplo) y lo incluimos en un nombre usando la función DESREF para que sea dinámico:

feriados =DESREF('matricial feriados'!$B$8,0,0,CONTARA('matricial feriados'!$B$8:$B$372),1)

La fórmula matricial es

=SI(O(inicio<=0,fin<=0,inicio>fin,ESNUMERO(inicio)=FALSO,ESNUMERO(fin)=FALSO),NOD(),SUMA(SI(ESERROR(COINCIDIR(DIASEM(FILA(INDIRECTO(inicio&":"&fin))),dias_excluidos,0)),SI(ESERROR(COINCIDIR(FILA(INDIRECTO(inicio&":"&fin)),feriados,0)),1,0)),0))


funcion DIAS.LAB mejorada
Recordemos una vez más que las fórmulas matriciales deben ser introducidas apretando simultáneamente Ctrl+Mayúsculas+Enter.

En lugar de las fórmulas matriciales propuestas por Pearson podemos crear una UDF (user defined functions, funciones definidas por el usuario).

En esta UDF el argumento “feriados” es opcional.


Function DiasLabMejorada(fecha_inicial As Date, _
                            fecha_final As Date, _
                            diasExcluidos As Range, _
                            Optional feriados As Range)
                            
'JLD Excel blog en Castellano
'Desarrollada por Jorge Dunkelman
'Junio 2009

    Dim daysCounter As Long, tmpDays As Date, dateCheck
    
    tmpDays = fecha_inicial + 1
    daysCounter = 0
    
    On Error GoTo ErrorHandler
    
    If feriados Is Nothing Then
    Do While tmpDays < fecha_final
        dateCheck = " "
        dateCheck = WorksheetFunction.Match(Weekday(tmpDays, vbMonday), diasExcluidos, 0)
        If Not IsNumeric(dateCheck) Then
            daysCounter = daysCounter + 1
        End If
        tmpDays = tmpDays + 1
    Loop
    
    Else
    
    Do While tmpDays < fecha_final
    dateCheck = " "
    dateCheck = WorksheetFunction.Match(CLng(tmpDays), feriados, 0)
    If Not IsNumeric(dateCheck) Then
        dateCheck = " "
        dateCheck = WorksheetFunction.Match(Weekday(tmpDays, vbMonday), diasExcluidos, 0)
        If Not IsNumeric(dateCheck) Then
            daysCounter = daysCounter + 1
        End If
    End If
        tmpDays = tmpDays + 1
    Loop
    End If
    
    DiasLabMejorada = daysCounter
    
   Exit Function
  
ErrorHandler:
dateCheck = "A"
Resume Next

    
End Function



Para usar esta UDF debemos guardarlo en un módulo común del editor de VB. Si lo hacemos en el cuaderno Personal.xls, podemos usar la función en todo cuaderno abierto.


En la próxima nota veremos como calcular la fecha final dados la fecha inicial y la cantidad de días hábiles a tomar en cuenta. Como en el caso de DIAS.LAB, también la función DIA.LAB toma en cuenta los sábados y domingos como días no laborales, parámetros que no pueden ser cambiados.

Technorati Tags:

6 comments:

miclasedemusica 18 febrero, 2011 00:14  

Muchas gracias nuevamente has sido mi salvacion, he ocupado la matricial de chip, pero sin los dias feriados, mas tarde la probare asi-

Anónimo,  18 marzo, 2011 01:17  

En serio que nos dejas sin palabras, y este blog es de ayuda permanente!, muchisimas gracias!!!

Anónimo,  16 mayo, 2011 12:37  

Muchas gracias por el blog.
Tengo un pequeño problema quería contar los lunes que hay entre dos fechas y con la fórmula matricial indicada en esta entrada lo he conseguido, pero lo que realmente quiero es que me cuente los lunes entre dos fechas pero descontando los lunes festivos que hay en ese periodo que tengo definidos en un rango de la misma hoja. Se trata de un calendario laboral y me piden el número de lunes y martes laborables que hay de aquí al final del año (lo tengo que mirar para cada trabajador en función de horarios y fechas de inicio y fin de contrato por lo que me gustaría hacerlo con fórmulas).
Un saludo y muchísimas gracias por toda la ayuda prestada directa e indirectamente.

Jorge L. Dunkelman 16 mayo, 2011 17:36  

Podrías usar la función definida por el usuario que publico en esta nota o si tienes instalado Excel 2010 la función DIAS.LAB.INTL.

Anónimo,  16 mayo, 2011 19:15  

Hola de nuevo, no entiendo lo que me quieres decir. Tengo excel 2003, ¿qué quieres decir con "usar la función definida por el usuario que publico en esta nota"?
Mil gracias de nuevo.
SARA

Jorge L. Dunkelman 16 mayo, 2011 20:29  

Sara,
tal como escribo en el comentario. La parte final de la nota muestra el código de una función definida por el usuario y también explica como usarla.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP