¿Cómo encontrar fechas de corte para una serie de fechas sin considerar sábados, domingos ni días festivos?
Tengo data histórica para muchos elementos. Cada data histórica tiene una fecha de inicio y una fecha final. Sin embargo, existe el riesgo de que me falten datos entre ambas fechas y deseo detectar las fechas faltantes obviando los sábados, domingos y días festivos. ¿Alguna idea
1 Respuesta
¿Y de qué manera quieres detectar las fechas faltantes?
Puedes poner un ejemplo de cómo tienes tus datos, y en ese ejemplo me dices cuáles fechas faltan y cómo quieres que te diga que esas fechas faltan.
Por ejemplo, tengo estas fechas
5ene15
6ene15
8ene15
En ese simple ejemplo me falta la fecha 7ene15, con una macro puedo saber que la fecha 7ene15 es la que falta, pero que hago, te la pongo en otra hoja, ¿o cómo?
Así de sencillo puedes explicar lo que requieres.
También es importante que me digas en dónde tienes las fechas de días festivos.
Ok. Gracias por la atención. Acá voy.
En esa hoja están los datos. Para código, tengo sus valores para cada fecha. Es una lista muy larga.
En esta hoja ("Resumen") va el resultado de la macro que intento hacer. Se muestra cada código con la fecha más antigua en que tengo registrado su valor (Fecha Inicio - columna E) y la fecha más reciente en que tengo registrado su valor (Fecha Fin - columna F).
Ahora, la macro que intento hacer debe detectar si faltan fechas dentro de cada intervalo de tiempo (Fecha Inicio - Fecha Fin) por cada código. Si faltan datos, la macro debe colocar las fechas en que sucedieron los cortes.
Siguiendo tu ejemplo, en Corte 1, en Inicio debe ir 06/01/2015 y en Fin debe ir 08/01/2015.
Si hay muchos cortes, la macro debe colocar todos, uno al lado de otro. En ese sentido, los títulos de Corte # se crearán automáticamente dependiendo cuántos hayan. Si hay códigos que no presentan cortes (no les faltan datos) y otros sí, habrán tantos títulos Corte # como tenga el máximo. Por lo tanto, habrán filas de Corte que estén vacías.
Te comento que son 1751 códigos y son 475 693 datos en total (la cantidad de datos va creciendo conforme pasa el tiempo), así que sería agradable evitar comandos que sean lentos.
Es todo eso. Me gusta programar y, por eso, me gustaría que le pongas comentarios para yo aprender, por favor. Este trabajo ya me está tomando varios días.
Agradezco enormemente tu apoyo y entendería que no pudieras hacer todo por temas de disponibilidad de tiempo. La parte que más me interesa es el rellenar el cuadro con las fechas de corte. El tema de los títulos yo lo podría hacer una vez que tenga todos los cortes existentes.
Gracias.
Cierto, los días festivos están en la hoja "Festivos".
Basta que pueda reconocer los cortes para un código y se podrá hacer para el resto con un bucle que vaya hasta el número de filas de "Datos_Total".
set d_total=Worksheets("Datos_Total")
filas=d_total.Rows.Count
for i =0 to filas - 1
(Programación)
Next i
Gracias de antemano.
Te anexo la macro documentada, revisa los sábados y domingos. Falta lo de los días festivos (te faltó indicar en dónde los tienes).
Falta realizar varias pruebas, como cuando faltan 2 días seguidos ó 3 ó 4 ó varios días. Realiza varias pruebas con varios códigos y me comentas de los resultados.
Sub Cortes()
'Por.Dante Amor
Set h1 = Sheets("Datos_Total")
Set h2 = Sheets("Resumen")
'limpia todo desde A4
h2.Range(h2.[a4], h2.[a4].SpecialCells(11)).ClearContents
u = h1.Range("A" & Rows.Count).End(xlUp).Row
'
For i = 2 To u
alias = h1.Cells(i, "A")
'fila de la primera fecha
ini = i
'busca la fila de la última fecha
Set b = h1.Range("A" & i + 1 & ":A" & u).Find(alias, lookat:=xlWhole, SearchDirection:=xlPrevious)
If Not b Is Nothing Then fin = b.Row Else fin = ini
'
'Pone los datos
k = h2.Range("D" & Rows.Count).End(xlUp).Row + 1
If k < 4 Then k = 4
h2.Cells(k, "D") = alias
h2.Cells(k, "E") = h1.Cells(ini, "B")
h2.Cells(k, "F") = h1.Cells(fin, "B")
'
'revisa las fechas de la fila inicial a la fila final
For j = ini To fin - 1
'revisa si es viernes
n = 1
If Weekday(h1.Cells(j, "B")) = 6 Then n = 3
'
'revisa si falta el día siguiente
If h1.Cells(j, "B") + n <> h1.Cells(j + 1, "B") Then
m = h2.Cells(k, Columns.Count).End(xlToLeft).Column + 1
h2.Cells(k, m) = h1.Cells(j, "B")
h2.Cells(k, m + 1) = h1.Cells(j, "B") + 2
End If
Next
'empiza con el siguiente alias
i = fin
Next
End Sub
Hola Dante, muchas gracias por la programación.
Recién la leo y acabo de probarla. Funciona un 90% bien. Acá te mando pantallazos de la prueba para cuando falta 2 y 3 días.
En el lado izquierdo están mis datos para la prueba. Lo de verde indicaría el inicio del corte y lo de rojo indica el fin del mismo. Al lado derecho están los datos que he borrado.
Acá está el cuadro donde están los resultados. Para el primer corte, la fecha fin debió ser 03/07/2012 y no 02/08/2012 como muestra la tabla.
Además, cuando faltan 3 o más días, siempre me ha salido como fin de corte el 16/08/2012. En esta prueba, la fecha fin del corte debería de ser 20/08/2012.
En otras pruebas siempre me ha aparecido la fecha fin de corte igual a la fecha inicio más dos días.
Además me gustaría que el resultado salga dd/mm/yyyy.
Esos son los únicos percances de la programación. ¿Crees que puedas darme una manito con eso? Gracias.
P.D: De todas maneras planeo valorar esta respuesta, descuida.
Prueba con la siguiente macro:
Sub Cortes()
'Por.Dante Amor
Set h1 = Sheets("Datos_Total")
Set h2 = Sheets("Resumen")
'limpia todo desde A4
h2.Range(h2.[a4], h2.[a4].SpecialCells(11)).ClearContents
u = h1.Range("A" & Rows.Count).End(xlUp).Row
'
For i = 2 To u
alias = h1.Cells(i, "A")
'fila de la primera fecha
ini = i
'busca la fila de la última fecha
Set b = h1.Range("A" & i + 1 & ":A" & u).Find(alias, lookat:=xlWhole, SearchDirection:=xlPrevious)
If Not b Is Nothing Then fin = b.Row Else fin = ini
'
'Pone los datos
k = h2.Range("D" & Rows.Count).End(xlUp).Row + 1
If k < 4 Then k = 4
h2.Cells(k, "D") = alias
h2.Cells(k, "E") = h1.Cells(ini, "B")
h2.Cells(k, "F") = h1.Cells(fin, "B")
'
'revisa las fechas de la fila inicial a la fila final
For j = ini To fin - 1
'revisa si es viernes
n = 1
If Weekday(h1.Cells(j, "B")) = 6 Then n = 3
'
'revisa si falta el día siguiente
If h1.Cells(j, "B") + n <> h1.Cells(j + 1, "B") Then
m = h2.Cells(k, Columns.Count).End(xlToLeft).Column + 1
h2.Cells(k, m) = h1.Cells(j, "B")
h2.Cells(k, m + 1) = h1.Cells(j + 1, "B")
End If
Next
'empiza con el siguiente alias
i = fin
Next
End SubEl formato del resultado depende del formato que tienes en la celda, simplemente cambia el formato de todas las columnas a "dd/mm/yyyy"
Dante, acabo de leer detenidamente tu macro y ya encontré la solución. En tu macro dice:
If h1.Cells(j, "B") + n <> h1.Cells(j + 1, "B") Then
m = h2.Cells(k, Columns.Count).End(xlToLeft).Column + 1
h2.Cells(k, m) = h1.Cells(j, "B")
h2.Cells(k, m + 1) = h1.Cells(j, "B") + 2
End If
Con la línea en negrita, siempre me va a dar como fecha fin de corte, la fecha de inicio de corte más dos días. Lo que debió decir es:
h2.Cells( k, m + 1) = h1.Cells( j + 1, "B")
De esta manera, si la fecha siguiente no es el día laborable que sigue, entonces la fecha anterior es la fecha de inicio de corte y la fecha que le sigue es la fecha de fin de corte.
Acabo de correrlo con mi corrección y tengo lo siguiente:
Lo corrí con los 1751 códigos y son 475 693 datos que tengo y no se demoró mucho (poco menos de minuto y medio) porque les puse unos comandos para acelerarlo.
Como verás, aún queda con el inconveniente de Navidad, Año Nuevo y Semana Santa. Las fechas están en la hoja "Festivos". ¿Me darías una mano para evitar que sean considerados como fecha faltante?
Gracias.
P.D: Acabando esto, postearé la macro final para compartirla con todos. Gracias.
Gracias Dante.
Qué bien! También lo vi. Ya publiqué la macro completa.
Y... ¿Cómo hago con los días festivos? Aún son considerados como fechas faltantes y no deberían.
Gracias.
Saludos,
Cómo consideras el día festivo.
El día festivo aparece en la hoja "Datos_Total", o en la hoja "Datos_Total" nunca aparecen.
Dame una idea, en dónde piensas poner los días festivos, en algunas celdas, de alguna hoja tienes que poner las fechas, que para tu empresa son días festivos, eso días no deberían aparecer en la hoja "Datos_Total".
Si puedes, modifica la macro y empieza a probar, avísame en dónde te atoras para revisarlo.
Hola Dante,
En los pantallazos que envié verás que hay una hoja que dice festivo. En esa hoja he puesto las fechas que corresponde a Navidad, Año Nuevo y Viernes Santo (las únicas fechas en que no se realiza cálculos en mi trabajo).
En lo que consiste la macro es revisar si el día laborable siguiente está o no. Para ello primero es descartar sábado o domingo. Esta tarea ya está.
Lo que aún falta es no considerar los días festivos. lo que yo tenía en mente es que una vez se detecte que la fecha siguiente no es fin de semana, coger esa fecha y buscarla en la Hoja "Festivos". Si esa fecha aparece en esa hoja entonces debe pasar a la siguiente fecha obviándolo como caso de dato faltante. Si esa fecha no es encontrada en la hoja "Festivos", entonces analizar si realmente es el día laborable siguiente.
Ejemplos:
Si tengo:
24/12/2012
26/12/2012
en la tabla no debe aparecer el 24dic como fecha inicio corte ni el 26dic debe aparecer como fecha fin corte pues el 25dic es feriado.
Si tengo:
24/12/12
27/12/12
Falta un día laborable: 26dic, entonces la fecha inicio de corte debe ser 24/12/2012 y la fecha fin de corte debe ser 27/12/2012.
Lo mismo sucedería con el 01/01/2012 pues es un día festivo. el que aparezca:
31/12/2011
02/01/2012
No debería de generar ningún corte en la tabla.
Espero que mi explicación haya sido buena. Este paso es el único que me falta.
Para los títulos de cortes tengo planeado contar las columnas usadas (h2.UsedRange.Columns.Count) y hacer un Step 2 para poner "Corte #" y otro bucle pequeño para poner "Inicio" y "Fin".
Saludos
En la hoja "Datos_Total" nunca parecen las fechas de los días festivos.
Gracias.
Saludos.
Te anexo la macro para los días festivos. Deberás poner tus días festivos en la hoja "festivos", columna "A".
Sub Cortes()
'Por.Dante Amor
Set h1 = Sheets("Datos_Total")
Set h2 = Sheets("Resumen")
Set h3 = Sheets("Festivos")
'
'limpia todo desde A4
h2.Range(h2.[a4], h2.[a4].SpecialCells(11)).ClearContents
u = h1.Range("A" & Rows.Count).End(xlUp).Row
'
For i = 2 To u
alias = h1.Cells(i, "A")
'fila de la primera fecha
ini = i
'busca la fila de la última fecha
Set b = h1.Range("A" & i + 1 & ":A" & u).Find(alias, lookat:=xlWhole, SearchDirection:=xlPrevious)
If Not b Is Nothing Then fin = b.Row Else fin = ini
'
'Pone los datos
k = h2.Range("D" & Rows.Count).End(xlUp).Row + 1
If k < 4 Then k = 4
h2.Cells(k, "D") = alias
h2.Cells(k, "E") = h1.Cells(ini, "B")
h2.Cells(k, "F") = h1.Cells(fin, "B")
'
'revisa las fechas de la fila inicial a la fila final
For j = ini To fin - 1
'revisa si es viernes
n = 1
If Weekday(h1.Cells(j, "B")) = 6 Then n = 3
'
'Revisa si el siguiente día es festivo
sigdia = h1.Cells(j, "B") + n
Set b = h3.Columns("A").Find(sigdia)
If Not b Is Nothing Then
n = n + 1
If Weekday(h1.Cells(j, "B") + n) = 7 Then n = n + 2
If Weekday(h1.Cells(j, "B") + n) = 1 Then n = n + 1
End If
'
'revisa si falta el día siguiente
If h1.Cells(j, "B") + n <> h1.Cells(j + 1, "B") Then
m = h2.Cells(k, Columns.Count).End(xlToLeft).Column + 1
h2.Cells(k, m) = h1.Cells(j, "B")
h2.Cells(k, m + 1) = h1.Cells(j + 1, "B")
End If
Next
'empiza con el siguiente alias
i = fin
Next
End SubSaludos.
Si necesitas ayuda para los títulos de los cortes, crea una nueva pregunta.
¡Gracias Dante!
Ya terminé con los títulos "Corte # ". No era necesario quitarte más tiempo pues no era complicado.
Gracias por el apoyo, tío.
Cualquier otra complicación, trataré de contactarme contigo.
Saludos,
