OBTENER DÍAS NO TRABAJADOS EN UN PERIODO DE TIEMPO DETERMINADO

Hola a todos!.

Si recordáis en el post anterior hablamos de la utilidad de la función DIAS.LAB a la hora de obtener días laborables en un determinado periodo de tiempo. Pues bién y fruto de una petición que me han realizado, he decidido hacer un ejercicio práctico pero para obtener los días no trabajados durante un periodo de tiempo determinado.

Si lo pensáis detenidamente, el cálculo de días laborables, nos va a servir tanto para conocer los días no trabajados como trabajados, en función de si los rangos de fecha que tenemos corresponden a trabajados o no trabajados.

Sin embargo, cuando hablamos de días no trabajados, normalmente se hace referencia a «días no trabajados en un determinado periodo de tiempo«. Por ejemplo si se quiere saber el total de días no trabajado en un trimestre: ej «01/01/2019 al 31/03/2019» y tenemos que el rango de días no trabajados de esa persona ha sido del 01/02/2019 hasta el 04/04/2019 Pues bien, los días serán en realidad para el primer trimestre del 01/02/2019 al 31/03/2019, obviando la parte del mes de abril (y los fines de semana y festivos).

Para lograr esto, o bien previamente se filtran las fechas y se modifica la fecha final o inicial según corresponda o se realiza un proceso programado. En este post vamos a hacer un proceso programado:

Vamos con un ejemplo:

Aquí tenemos el rango de días no trabajados de cada persona y todos los festivos del año. El ejercicio será obtener el total de días no trabajados durante el primer trimestre, teniendo en cuenta las fechas de inicio y final y los casos en los que la fecha final está en blanco, es decir, que sigue activa en el presente.

Para facilitar el ejercicio he decidido crear un formulario donde se podrá indicar el rango sobre el que queremos extraer los días, en este ejemplo el trimestre (para ampliar presionar sobre la imagen):

Como podéis observar, en el formulario podemos incluir una fecha inicio y otra final, estas fechas serán sobre las que se calculen los días (el formulario está programado para solo incluir fechas, si se introduce un texto o número que no sea fecha, mostrará un mensaje de advertencia).

Cuando presionamos en «CALCULAR DÍAS EN PERIODO» la rutina va a verificar que el rango de los días de cada persona encaja en la fecha indicada en el formulario, de forma que va capturar la información y pasar el nuevo rango a las columnas E y F respectivamente. Observad que en el caso de la fecha abierta, el proceso la marca en rojo y la cierra a fecha fin de nuestro formulario (como debe ser).

O en el caso Inés Chover, donde la fecha final se modifica de 01/04/2019 a «31/03/2019», dado que no queremos conocer los días en Abril. En caso de ser al contrario e incluir una fecha de inicio ej: en 2018, haría lo mismo y la modificaría a 01/01/2019 como incio.

La macro que realiza esto (es un poco extensa y bastante comentada) es esta, y se ejecuta con el botón «CALCULAR DÍAS EN PERIODO»:

Private Sub CommandButton1_Click()
'DECLARAMOS VARIABLES
Dim INI_PER As Date
Dim FIN_PER As Date
Dim i As Long, fin As Long, j As Long, nCol As Long
Dim v_fecha_1 As Date, v_fecha_2 As Date, rango As Range
With Sheets("Hoja1")
'Si no introducimos una fecha en el formulario, salimos del proceso
v_fecha_1 = IsDate(UserForm1.TextBox1.Value)
v_fecha_2 = IsDate(UserForm1.TextBox2.Value)
If v_fecha_1 = False Or v_fecha_2 = False Then
MsgBox ("DEBES INTRODUCIR UNA FECHA SIGUIENDO EL SIGUIENTE FORMATO DD/MM/AAAA"), vbExclamation, "ERROR EN DATOS"
Exit Sub
ElseIf UserForm1.TextBox1.Value > UserForm1.TextBox2.Value Or UserForm1.TextBox2.Value < UserForm1.TextBox1.Value Then
MsgBox ("VERIFICA EL VALOR DE LAS FECHAS INTRODUCIDAS"), vbExclamation, "ERROR EN DATOS"
Exit Sub
End If
'Pasamos el valor del formulario a variables de inicio y fin de periodo
INI_PER = UserForm1.TextBox1.Value
FIN_PER = UserForm1.TextBox2.Value
fin = Application.CountA(Sheets("Hoja1").Range("A:A"))
.Range("E2:G" & fin + 1).Clear
For j = 2 To fin
If .Cells(j, 4).Interior.Color = vbRed Then
.Cells(j, 4) = vbNullString
.Cells(j, 4).Interior.Pattern = xlNone
End If
Next j
'Marcamos celdas vacías en rojo, si fecha inicio están en el rango
'cerramos la fecha con FIN (USERFORM) sino, dejamos registro vacío
For i = 2 To fin
If .Cells(i, 4) = Empty And .Cells(i, 3) <= FIN_PER Or .Cells(i, 4) = Empty And .Cells(i, 3) >= INI_PER And .Cells(i, 3) <= FIN_PER Then .Cells(i, 4) = FIN_PER .Cells(i, 4).Interior.Color = vbRed ElseIf .Cells(i, 4) = Empty And .Cells(i, 3) > FIN_PER Then
.Cells(i, 4) = " "
.Cells(i, 4).Interior.Color = vbRed
End If
'Si fecha inicial es menor o igual a INICIO (USERFORM) y fecha fin mayor o igual que FIN (USERFORM),
'Indicamos valores de INICIO y FIN
If .Cells(i, 3) <= INI_PER And .Cells(i, 4) >= FIN_PER And .Cells(i, 4) <> " " Then
.Cells(i, 5) = INI_PER
.Cells(i, 6) = FIN_PER
'Si fecha inicial es mayor o igual a INICIO (USERFORM) y fecha fin menor o igual que FIN (USERFORM),
'Indicamos valores de fecha inicio y fecha fin
ElseIf .Cells(i, 3) >= INI_PER And .Cells(i, 4) <= FIN_PER And .Cells(i, 4) <> " " Then
.Cells(i, 5) = CDate(.Cells(i, 3))
.Cells(i, 6) = CDate(.Cells(i, 4))
'Si fecha inicio es mayor o igual que INICIO (USERFORM) y menor o igual que FIN (USERFORM) y fecha fin es mayor o igual que FIN (USERFORM)
'Dejamos fecha inicio y cerramos con FIN (USERFORM)
ElseIf .Cells(i, 3) >= INI_PER And .Cells(i, 3) <= FIN_PER And .Cells(i, 4) >= FIN_PER And .Cells(i, 4) <> " " Then
.Cells(i, 5) = CDate(.Cells(i, 3))
.Cells(i, 6) = FIN_PER
'Si fecha inicio es menor o igual que INICIO (USERFORM) y menor o igual que FIN (USERFORM)y fecha fin es menor o igual que FIN (USERFORM)
'y fecha fin es mayor o igual que INICIO (USERFORM)
'Abrimos con INICIO (USERFORM) y cerramos con fecha_fin
ElseIf .Cells(i, 3) <= INI_PER And .Cells(i, 3) <= FIN_PER And .Cells(i, 4) <= FIN_PER And .Cells(i, 4) >= INI_PER And .Cells(i, 4) <> " " Then
.Cells(i, 5) = INI_PER
.Cells(i, 6) = CDate(.Cells(i, 4))
End If
'Calculamos días hábiles descontando vacaciones o festivos en el periodo
nCol = .Cells(i, Cells.Columns.Count).End(xlToLeft).Column
'Seleccionamos el rango de los días de vacaciones
ActiveSheet.Range(.Cells(i, 8), .Cells(i, (8 + (nCol - 8)))).Select
Set rango = Selection
If .Cells(i, 5) <> Empty Then .Cells(i, 7) = Application.WorksheetFunction.NetworkDays(CDate(.Cells(i, 5)), CDate(.Cells(i, 6)), rango)
Next i
End With
End Sub


Con el botón «RESTABLECER», preparamos la hoja para una nueva extracción de la información, borrando los rangos y los días calculados y dejando en blanco la fecha abierta.

Private Sub CommandButton2_Click()
Dim fin As Long, i As Long
With Sheets("Hoja1")
With UserForm1
.TextBox1 = Empty
.TextBox2 = Empty
End With
fin = Application.CountA(Sheets("Hoja1").Range("A:A"))
.Range("E2:G" & fin + 1).Clear
For i = 2 To fin
If .Cells(i, 4).Interior.Color = vbRed Then
.Cells(i, 4) = vbNullString
.Cells(i, 4).Interior.Pattern = xlNone
End If
Next i
End With
End Sub

Como habéis podido observar, este procedimiento nos va servir para adaptarlo a todo tipo de cálculo con fechas y periodos de tiempo. El tema de los condicionales, es un poco lioso, pero necesario para identificar todas las casuísticas que se pueden dar entre las fechas y rangos, creo que he identificado todas las posibilidades (racionales) que se pueden dar, obviamente una fecha fin menor que una fecha inicio será un error en vuestra base de datos y el resultado saldrá negativo (así podréis verificar si la calidad de los datos es correcta, por lo menos en lo que a fechas se refiere).

Dado que tener en cuenta los días festivos o que no se deben tener en cuenta es importante, la macro también tiene los tiene en cuenta y los descuenta de los días totales.

Y eso es todo, creo que es un post muy interesante y útil dado que ahorra mucho de tiempo de trabajo y de repaso de la información.

Descarga el archivo de ejemplo pulsando en: OBTENER DÍAS NO TRABAJADOS EN UN PERIODO DE TIEMPO DETERMINADO

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.



¡¡Muchas gracias!!

Mediante la suscripción al blog, la realización comentarios o el uso del formulario de contacto estás dando tu consentimiento expreso al tratamiento de los datos personales proporcionados según lo dispuesto en la ley vigente (LOPD). Tienes más información al respecto en esta página del blog: Política de Privacidad y Cookies