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.
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.
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)
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))
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))
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: MS Excel
