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:

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

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):

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

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.

Donate Button with Credit Cards

¡¡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

Anuncios

PROGRAMAR LA FUNCIÓN DIAS.LAB CON VBA Y AUTOMATIZAR DÍAS DE VACACIONES

Hola a todos!

Hace unos días respondía a la consulta que me enviaba un lector sobre la posibilidad de programar la función DIAS.LAB (os dejo el enlace a la microsoft donde se explica esta fórmula).

Tal y como se expone en la web, su función es la siguiente: “Devuelve el número de días laborables entre fecha_inicial y fecha_final. Los días laborables no incluyen los fines de semana ni otras fechas que se identifiquen en el argumento vacaciones.”

Su sintaxis es: Fecha Inicial, Fecha Final , Vacaciones (o días festivos).

Pues bien, se puede automatizar y además incluyendo el rango de días festivos o de vacaciones que hayamos indicado.

Veamos un ejemplo con unas cuantas personas (clic en la imagen para ampliar):

PROGRAMAR LA FUNCIÓN DIAS.LAB CON VBA Y AUTOMATIZAR DÍAS DE VACACIONES

Como podéis observar estoy teniendo en cuenta una fecha inicial y final para todos igual, es decir, todo el año 2019. Y los días de vacaciones distintos para cada uno de ellos.

El objetivo es calcular los días laborables del año descontando los días festivos/vacaciones (columnas “F” y posteriores) y completar la columna “E”.

Esto lo vamos a hacer con la siguiente macro:

Sub DIAS_LAB()
'Declaramos variables
Dim i As Long, Final As Long
Dim Rango As Range, nCol As Long
With Sheets("Hoja1")
Final = Application.CountA(.Range("A:A"))
'Con un loop recorremos cada registro
For i = 2 To Final
'Detectamos la última celda con datos de cada fila
nCol = .Cells(i, Cells.Columns.Count).End(xlToLeft).Column
'Seleccionamos el rango de los días de vacaciones
ActiveSheet.Range(.Cells(i, 6), .Cells(i, (6 + (nCol - 6)))).Select
Set Rango = Selection
'Calculamos los días
.Cells(i, 5) = Application.WorksheetFunction.NetworkDays(CDate(.Cells(i, 3)), CDate(.Cells(i, 4)), Rango)
Next i
End With
End Sub

Para poder capturar automáticamente los días de vacaciones, ha optado por utilizar un rango variable que funciona gracias a la detección de la última celda de cada fila que contiene datos. El resto simplemente es completar los argumentos de la función “NetworkDays”.

El resultado es el siguiente (clic en la imagen para ampliar):

PROGRAMAR LA FUNCIÓN DIAS.LAB CON VBA Y AUTOMATIZAR DÍAS DE VACACIONES_1

Como podéis observar, ya hemos calculado el total de días por persona.

Este es solo un ejemplo de programación, se podría utilizar la propia función DIAS.LAB para calcular los días de vacaciones en un rango inicial y final (de mismo que para los días laborales, pero de vacaciones), evitando así el tener que indicar día por días las vacaciones.

Y eso es todo, espero que os sea de utilidad : )

Descarga el archivo de ejemplo pulsando en: PROGRAMAR LA FUNCIÓN DIAS.LAB CON VBA Y AUTOMATIZAR DÍAS DE VACACIONES

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

Donate Button with Credit Cards

¡¡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

MOSTRAR RELACIÓN DE FECHAS ENTRE DOS FECHAS

Hola a todos!.

Hace unos días me hacían una consulta a través de LinkedIn que trataba sobre cómo se podía generar automáticamente todas las fechas comprendidas entre dos fechas (inicial y final).

Aunque esto es posible realizarlo mediante fórmulas, creo que lo más oportuno es hacerlo con VBA, ya no solo por la rapidez sino por todas las posibilidades que ofrece para luego poder pasar estos datos a un formulario con cuadros de lista o combos.

Para este ejercicio voy a plantear dos situaciones, una en la que queremos obtener todas las fechas y otra en la que queremos obtener solo los días hábiles y omitiendo también festivos.

Veamos la primera, obtener todas las fechas, para ello imaginad que tenemos las siguiente fechas en nuestra hoja Excel:

MOSTRAR RELACION DE FECHAS ENTRE DOS FECHAS

y queremos obtener todas las fechas que comprenden desde el 01/05/2018 hasta el 15/05/2018. Para poder hacerlo vamos a utilizar esta macro:

Sub obtener_fechas()
'Declaramos variables
Dim inicio As Date, fin As Date, nCont As Date
Dim sCadena As String, fsem As String
Dim matriz As Variant, j As Double
With Sheets("FECHAS")
'Indicamos las celdas con la fecha inicio y fin
inicio = .Cells(1, 2)
fin = .Cells(2, 2)
'Mediante un loop añadimos un día a la fecha inicial
'y guardamos fechas en la variable sCadena
Do While inicio < fin - 1
nCont = DateAdd("w", 1, inicio)
inicio = nCont
sCadena = sCadena & " " & inicio
Loop
'pasamos la información de la matriz a la hoja
matriz = Split(sCadena, " ")
For j = 1 To UBound(matriz)
.Cells(j, 3) = Format(matriz(j), "mm/dd/yyyy")
Next j
End With
End Sub

Como podéis observar, mediante un loop vamos añadiendo días a la fecha inicial de forma que vamos componiendo cada fecha hasta llegar a la fecha final. Esos datos los guardamos en una variable string como una cadena de texto que luego pasaremos a una matriz y finalmente a la hoja, este es el resultado:

MOSTRAR RELACION DE FECHAS ENTRE DOS FECHAS1

Ahora vamos con la segunda situación, es decir, queremos obtener sólamente los días hábiles y excluir festivos, para ello utilizaremos la misma macro pero con unas modificaciones:

Sub obtener_fechas_dias_lab()
'Declaramos variables
Dim inicio As Date, fin As Date, nCont As Date, dfest As Date
Dim sCadena As String, fsem As String, j As Double
Dim matriz As Variant
With Sheets("FECHAS")
'Indicamos las celdas con la fecha inicio y fin
inicio = .Cells(1, 2)
fin = .Cells(2, 2)
'Mediante un loop añadimos un día a la fecha inicial
'y guardamos fechas en la variable sCadena
Do While inicio < fin - 1
nCont = DateAdd("w", 1, inicio)
inicio = nCont
fsem = Format(inicio, "ddd")
dfest = CDate(.Cells(3, 2))
'Si el día es un sábado o domingo o un festivo no guardamos fecha
If fsem "sá." And fsem "do." And inicio dfest Then
sCadena = sCadena & " " & inicio
End If
Loop
'pasamos la información de la matriz a la hoja
matriz = Split(sCadena, " ")
For j = 1 To UBound(matriz)
.Cells(j, 4) = Format(matriz(j), "mm/dd/yyyy")
Next j
End With
End Sub

Para poder excluir los sábados y los domingos es necesario que podamos detectar los días en las fechas que vamos generando en el loop, esto lo hacemos con:

Format(inicio, "ddd")

Donde los sábados se representan como: “sá.” y los domingos como “do.“, es obvio que si estamos trabajando con otros idiomas debemos modificar e introducir los datos correspondientes . Con un condicional excluimos los sábados y los domingos y también la fecha del día festivo, finalmente la información es la siguiente:

MOSTRAR RELACION DE FECHAS ENTRE DOS FECHAS2

Y con esto ya hemos finalizado el ejercicio, ya podemos obtener los días entre dos fechas y listalos, (con o sin días laborales).

Espero que os resulte de utilidad!.

Descarga el archivo de ejemplo pulsando en: MOSTRAR RELACIÓN DE FECHAS ENTRE DOS FECHAS

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

Donate Button with Credit Cards

¡¡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