por Hola a todos!, parece que este fin de semana le ha tocado a Power Query!.
El post de hoy trata sobre un tema que tenía pendiente desde hace tiempo, y es el de crear una función definida por el usuario (UDF) o si preferís custom function, que nos permita calcular días hábiles entre dos fechas, y además tenga en cuenta una lista de fechas que pueden ser tratadas como vacaciones o festividades.
Allá por Agosto del año pasado escribí un post realizando este ejercicio pero calculando el dato en una columna personalizada con lenguaje M: CALCULAR DÍAS LABORABLES CON POWER QUERY
En esta ocasión lo vamos a hacer pero con una función. Veamos los datos que necesitaremos inicialmente, datos de fechas (inicio y final) y listado de días festivos.
Estas tablas las vamos a subir por separado a Power Query.
Y a continuación en una consulta en blanco vamos a añadir el siguiente código en nuestro editor de Query:
(Inicio as date, Fin as date, optional Festividades as list) =>
let
Fechas = List.Dates(Inicio, Duration.Days(Fin - Inicio)+1, #duration(1, 0, 0, 0)),
nFechas=List.Difference(Fechas, Festividades),
DiasLab = List.Select(nFechas, each Date.DayOfWeek(_,1) < 5 ),
nDias = List.Count(DiasLab)
in
nDiasQuedaría así:
y a la consulta le llamaré DIASLAB. Después de pulsar en el botón «Listo», tendremos nuestra función ya creada:
Una vez que ya hemos creado la función, ya la podemos usar .: Abrimos MITABLA y seleccionamos las columnas INICIO y FIN y a continuación pulsamos en «Invocar función personalizada». Entonces ya podremos especificar el nombre de nuestra función DIASLAB e incluir los campos INICIO y FIN. También debemos añadir el campo de las FESTIVIDADES.
El resultado lo vamos a exportar a una hoja que he denominado RESULTADO:
Y esto es todo. Os dejo el archivo:
Y esto es todo, espero que sea de utilidad!!.
¿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
Buenos días Segu,
Gracia por seguir enriqueciéndonos siempre con éste y todos los grandes aportes de tu canal. La función es muy útil, pero está limitada a una sola lista de festividades, es decir sólo puedes aplicarla para todos los que sean festivos nacionales, por tanto se limita y no puedes aplicarla para los festivos de cada provincia (que sería lo correcto). La función debería permitir que según el código postal de su provincia (es decir tener una tabla con las columnas de cada provincia y sus festivos en lugar de sólo la nacional) eligiera dicha lista y la aplicara al periodo indicado de fechas de nuestra tabla.
Para aquellos que no tenemos grandes conocimientos, la solución sería hacer condiciones If – else condicionado a un campo de código postal, siendo éste el que determine qué columna de nuestra tabla de festividades debe elegir, pero supondrá hacer 52 comparaciones (una por cada columna de los festivos de las distintas provincias ) no siendo una opción muy funcional de aplicar.
Si tu aporte de función definida por el usuario fuera teniendo presente los festivos de las 52 provincias de España, creo que la misma sería de mayor interés e utilidad para todos nosotros.
Agradecerte todos tus aporte y conocimientos compartidos en tu Web.
Un saludo.
Hola Francisco,
He creado una rutina en VBA que resuelve la necesidad que indicas: https://excelsignum.com/2022/11/15/calcular-dias-laborables-para-multiples-rangos-de-vacaciones-o-festivos/
En breve espero replicarla en Power Query (necesito tiempo para estudiar una solución en lenguaje M). De todas formas en VBA funciona perfectamente y sin problema.
Saludos
Buenas noches,
Muchas gracias por tu rápida respuesta y el aporte en VBA facilitado, funciona muy bien y resuelve ésta necesidad perfectamente; desde luego que nos ayudará a muchos el poder utilizarla.
Ahora te animo a ese reto de replicarlo en Power Query (en lenguaje M), lenguaje con un gran futuro en la transformación y modelado de datos y en el que todos seguimos aprendiendo y sorprendiéndonos contigo.
Agradecerte de nuevo este gran aporte.
Un saludo.
Muchísimas gracias por su anuencia a compartir conocimiento tan valioso. Le saludo de Costa Rica.
Gracias José!!
Buena aportación. Una consulta, si en vez de días quisiera calcular las horas entre dos fechas teniendo en cuenta días festivos y una jornada laboral de 8-17 hrs cómo podría hacer? he llegado solo hasta discriminar días festivos mas no la jornada laboral. Aquí dejo mi código.
Let
FechaInicio = [Fecha de creación personalizada],
FechaTermino = [Fecha_Hora_Termino],
DuracionTotal = Duration.From(FechaTermino – FechaInicio),
HorasLaborables =
List.Accumulate(
List.DateTimes(FechaInicio, Number.From(DuracionTotal), #duration(1, 0, 0, 0)),
#duration(0, 0, 0, 0),
(state, current) =>
if Date.DayOfWeek(current) = 6 or
Date.DayOfWeek(current) = 7
then
state
else
let
HoraActual = Time.From(current),
HoraInicio = Time.From(HoraInicioJornada),
HoraFin = Time.From(HoraFinJornada)
in
if HoraActual >= HoraInicio and HoraActual < HoraFin
then
state + #duration(0, 1, 0, 0) // Agregar 1 hora a las horas laborables
else
state
),
HorasFestivos =
List.Sum(
List.Transform(
List.DateTimes(FechaInicio, Number.From(DuracionTotal), #duration(1, 0, 0, 0)),
each if List.Contains(FESTIVIDADES[FESTIVOS], _) then #duration(0, 1, 0, 0) else #duration(0, 0, 0, 0)
)
),
HorasTotales =
let
TotalHoras = Duration.From(HorasLaborables),
Horas = Number.RoundDown(TotalHoras / #duration(1, 0, 0, 0)),
Minutos = Number.RoundDown((TotalHoras – #duration(Horas, 0, 0, 0)) / #duration(0, 1, 0, 0))
in
#duration(Horas, Minutos, 0, 0)
in
HorasTotales)