Ayuda!!! suma de dias de un rango de fechas que se traslapan o no

[fjchavez]

Hola, tengo un proyecto de programacion que involucra el calculo de dias entre dos fechas. La cuestion se complica debido a que tengo un array de fechas iniciales y otro de fechas finales. La idea es contar los dias en total sin contar las fechas que se traslapen.
Es decir si tengo las fechas:
row 1 ---> 11/03/2011 a 11/04/2011
row 2 ---> 15/03/2011 a 15/04/2011
row 3 ---> 20/04/2011 a 16/06/2011

Entonces se deberian contar los rangos de fechas del 11/03/2011 al 15/04/2011 mas el rango de fechas 20/04/2011 al 16/06/2011.
Como podria hacer eso via vba de excel o alguna orientacion de como programarlo.
Muchas gracias!.

[mrocf]

Lo resuelves con una fórmula MATRICIAL como la siguiente:

= SUMAPRODUCTO( B1:B3 - A1:A3 ) - SUMAPRODUCTO( SI( A2:A3<B1:B2; B1:B2 - A2:A3; 0) )

Si te equivocas y no ingresas la fórmula como matricial verás : #¡VALOR!

Saludos, Cacho.

[fjchavez]

Hola gracias, pero la idea es que lo tengo que hacer en vba y no para 3 filas sino que lo tengo que generalizar.

[mrocf]

Entiendo lo que dices... Pero, ¿Qué te impide generalizar la fórmula mostrada, tanto como implementarla en VBA?

Francamente: ¡es muy sencillo! pues la base ya la tienes.

[fjchavez]

Entiendo que para TI sea muy sencillo. Pero si por ejemplo agrego ademas de las filas que mencioné las siguientes:

row 1 ---> 11/03/2011 a 11/04/2011
row 2 ---> 15/03/2011 a 15/04/2011
row 3 ---> 20/04/2011 a 16/06/2011
row 4 ---> 26/11/2011 a 26/12/2011
row 5 ---> 17/07/2011 a 18/08/2011
row 6 ---> 12/02/2011 a 12/03/2011
row 7 ---> 16/06/2011 a 05/08/2011

ahi tu formula no me funciona. Y si además es para un rango de 100 a mas filas y donde las fechas no van en orden necesariamente. A eso me refiero con "generalizarlo".
Agradezco tu ayuda

[mrocf]

Asumiendo que las fechas se encontraran en las columnas A y B (al igual que lo que te mostrara en la fórmula base) y a partir de la celda A1, podrías intentar lo siguiente:

Código vb:

Sub Macro781()
Dim LastRow As Long, Q As Long
Application.ScreenUpdating = False
 
' Determino última fila
LastRow = [a1].End(xlDown).Row
 
' Defino "AL" como columna auxiliar. Para el cálculo necesito que la columna A
' esté ordenada.
' Al término del cálculo vuelvo al orden inicial mediante esta columna
[AL1] = 1: [AL1].Resize(LastRow).DataSeries Type:=xlLinear
 
' Ordeno según columna A
Range("A1:AL" & LastRow).Sort Key1:=[a1], Order1:=xlAscending, Header:=xlNo
 
' Fórmula Matricial en celda D1
[d1].FormulaArray = "= SUMPRODUCT( B$1:B" & LastRow & " - A$1:A" & _
  LastRow & " ) - SUMPRODUCT( IF( A$2:A" & LastRow & "<B$1:B" & _
  LastRow - 1 & ", B$1:B" & LastRow - 1 & " - A$2:A" & LastRow & ", 0) )"
 
' Traslado resultado a variable "Q"
Q = [d1]: [d1].ClearContents
 
' Ordeno según columna AL
Range("A1:AL" & LastRow).Sort Key1:=[AL1], Order1:=xlAscending, Header:=xlNo
 
' Elimino columna AL: cumplió su cometido
[AL1].EntireColumn.Delete
Application.ScreenUpdating = True
 
' Muestro el dato en D1
[d1] = Q
End Sub
 

Saludos, Cacho.

[JoaoM]

Soy algo ignorante en esto de macros, algunas para mi mismo, SIEMPRE pido ayuda por mi experiencia casi nula.

Solo quiero hacer una observacion:

Las fechas estan desordenadas, ¿que impide 1º ordenarlas y luego hacer la cuenta de los dias?

y ¿porque tiene que ser brincando fechas si ordenandolas 1º (como referi arriba) y luego contar desde la 1ª 11/03 hasta la ultima 26/12
Porque no entiendo el porque tiene que saltar fechas si quier saber la cantidad de dias entre la 1ª y ultima

row 1 ---> 11/03/2011 a 11/04/2011
row 2 ---> 15/03/2011 a 15/04/2011
row 3 ---> 20/04/2011 a 16/06/2011
row 4 ---> 26/11/2011 a 26/12/2011
row 5 ---> 17/07/2011 a 18/08/2011
row 6 ---> 12/02/2011 a 12/03/2011
row 7 ---> 16/06/2011 a 05/08/2011

Talves yo no esté entendiendo bien la palabra traslapan

[mrocf]

El concepto de "traslapar" está muy bien ejemplificado por fjchavez en su primer mensaje.

Y, de hecho: si las fechas no estuviesen traslapadas no sería necesario ordenar nada ni -mucho menos- una macro.

Pero lamentablemente: ¡no es el caso!...

[JoaoM]

mrof, no quise decir lo contrario, yo de construccion de macros o programacion, mis conocimientos son casi nulos.
Si por casualidad entendiste lo contrario, perdon, no era eso que queria preguntar.
Y si preguntar se trata, pues eso mismo fue lo que hice, que me expicaran cual el significado

[mrocf]

Por el contrario, amigo JoaoM: te entendí a la perfección y, por cierto, me diste "pie" para agregar un comentario adicional.

Si no te fuese suficiente el ejemplo mostrado en la pregunta inicial para comprender la idea del "traslape", pues hazte un paseíto por la RAE:

- Definición de traslapar

Saludos, Cacho.

[JoaoM]

Lo que pasa talves es que;
Al aplicar la macro me dá el resultado, 212. Estos 212 son referentes a que fechas? esto es lo que yo por mi falta de conocimiento, no logro ver.

Disculpa mi atrevimiento x las preguntas mias que perecieran tontas, no quiero que pienses en que estoy haciendo de tu persona, un tutor o profesor.
Explico mi resultado, no se si será cierto

Tengo fechas
row A1 ---> 11/03/2011 B1 11/04/2011
row A2 ---> 15/03/2011 B2 15/04/2011
row A3 ---> 20/04/2011 B3 16/06/2011
row A4 ---> 26/11/2011 B4 26/12/2011
row A5 ---> 17/07/2011 B5 18/08/2011
row A6 ---> 12/02/2011 B6 12/03/2011
row A7 ---> 16/06/2011 B7 05/08/2011

ORDENA las fechas alfabeticamente las 7 fechas A1 en AL (y X supuesto la B conjuntamente) para contar los dias de diferencia entre cada 2 fechas (ya ordenadas alfabeticamente) y, al final SUMA TODOS resultados de entre fechas y dá el resultado en D1 (o el que le ponga en [d5] = Q o H6 x ejemplo.
¿estaré en lo cierto?.

Lo cierto es que yo saco la cuenta de A y la respectiva en B manualmente y me dá 266, 1 mas 1 menos
X E. A1 con B1 me dá digamos que 30 dias
A2 con B2 me dá 30 tambien
A3 con B3 56
ect ect para un total de 266 aproximadamente y nunca 212, ESTA es mi incertidumbre, porque claro que al ordenar la columna A, las fechas de la columna B acompañan siempre su pareja en A, para donde vaia.

Acabo de hacer una prueba y fue:

Cambie la fecha row A2 ---> 15/03/2011 B2 15/04/2011
a B2 15/08/2011 adelantando 4 meses como puedes ver, 4 meses son 120 dias y la diferencia de antes a ahora solo me dá 5 dias, 212 a 217 y no 212 de antes + 120 = 332.

Por eso te digo y repito, no entiendo programacion así como no la sé hacer y manualmente darme eso, por mi ignorancia en programacion, hago TODAS ESTAS PREGUNTAS que pueden parecer tontas

[mrocf]

Esperemos unas horas para ver si el generador de la "criatura" tiene interés en ver resuelto su problema...

[fjchavez]

mrocf gracias por la ayuda, estaba revisando la formula, funcionaba perfectamente cuando derrepente me tope con esta serie de fechas!!!!

Para este caso:

row 0----->FECHA INICIO a FECHA FIN
row 1----->12-feb-11 a 12-mar-11
row 2----->12-feb-11 a 13-mar-11
row 3----->13-feb-11 a 13-mar-11
row 4----->15-feb-11 a 15-dic-11
row 5----->16-jul-11 a 17-ago-11

Esta en orden pero no me suma correctamente.

[mrocf]

Indica, por favor:

a) Cual es el resultado que entrega la macro (ello es para ver si la implementaste de un modo correcto);
b) Cuanto es lo que debería sumar según tus cálculos;
c) Y, más importante aún: ¿Cómo llegas a ese resultado?

[fjchavez]

Hola,

Adjunto una imagen de los calculos:


a) El resultado que me arroja es 186
b) Debería ser 307 (cuento el dia final - dia inicial +1 para incluir el extremo)
c)
-Llegue a ese resultado primero hago dia final - dia incial +1
-Luego evaluo las fechas, si hay alguna fecha que ya este incluida en otra no la cuento.
- Al final llego a 2 fechas:
12/02/2011 a 13/03/2011
15/02/2011 a 15/12/2011
que seria solo una :
12/02/2011 al 15/12/2011,
lo que me da como resultado 307

Todo este proceso será posible automatizarlo?

[mrocf]

¿Has entendido, Joao, a que se refiere fj con "traslapar"?...

[JoaoM]

Algo si, coincidencia, simultaneidad, superponer y algo mas segun dicionario

[mrocf]

Estuve mirando la nueva información que aporta fj y se advierte que cierto tipo de solapamiento de fechas (particularmente: las fechas de "fin") no pueden ser contempladas en una fórmula, por más compleja que ésta sea.

De modo que habrá que cambiar un aspecto del ángulo de análisis. Pregunto: ¿Es necesario que al término de la macro, la información quede ordenada como al inicio del proceso, o podría quedar ordenada según la fecha de inicio?

Saludos, Cacho.

[JoaoM]

Como dije y sigo repitiendo, yo en esto de programacion, NADA que ver pero ideas si. ¿Y sabes como se dice a alguien que tiene ideas? IDIOTA jejejejejejeje
Bien, ahora sin broma, en serio;

¿Se podria hacer macro en que en la columna C1 (XE.) colocara la diferencia entre las dos de A y B as fechas
row A1 ---> 11/03/2011 B1 11/04/2011 C1 30 (digo +-)
row A2 ---> 15/03/2011 B2 15/04/2011 C2 30 (digo +-)
ect ect

en C3 o D1 la suma (D1 60 dias) o C3 60 Dias?

¿podria ser?

solo una idea

[fjchavez]

En realidad si funciona el metodo sin k me regrese el orden original devuelta no importa, la idea es que me de el calculo correcto

[mrocf]

En ese caso se simplifican las cosas. Por ejemplo, para una estructura de datos como:


podrías intentar:

Código vb:

Sub Macro787()
Dim LastRow As Long, C As Range
Application.ScreenUpdating = False
' Determino última fila
LastRow = [a1].End(xlDown).Row
' Para el cálculo necesito que las columnas A y B estén ordenadas.
Range("A1:B" & LastRow).Sort Key1:=[a1], Order1:=xlAscending, _
  Key2:=[b1], Order2:=xlAscending, Header:=xlNo
[AM1:AN1] = [a1:b1].Value
 
For Each C In Range("A2:A" & LastRow)
  Select Case C < [an65536].End(xlUp)
    Case True
      If C.Offset(, 1) > [an65536].End(xlUp) Then [an65536].End(xlUp) = C.Offset(, 1)
    Case False
      [am65536].End(xlUp).Offset(1).Resize(, 2) = C.Resize(, 2).Value
  End Select
Next C
' Muestro el dato en D1
With [am65536].End(xlUp)
  [d1] = Evaluate("SUMPRODUCT( 1 + AN1:AN" & .Row & " - AM1:AM" & .Row & ")")
End With
 
' Elimino columnas AM:AN pues cumplieron su cometido
[AM1:AN1].EntireColumn.Delete
Application.ScreenUpdating = True
End Sub
 

¿Es lo que estabas buscando?
Saludos, Cacho.

[JoaoM]

Talves me esté metiendo en una cama con colchon de puras agujas.
Y digo esto porque el tema no es mio y tampoco se como aportar solucion, esta ultima es la principal razon por la cual, el colchon que me espera talves esté lleno de agujas.

Despues del resultado de los dos codigos, el anterior y ahora este el resultado es 212 y 216 respectivamente luego de ordenada la A como pueden observar

Luego hice un cambio en la fecha roja

15/03/2011 ----------15/04/2011 ------- 15/03/2011 ---15/07/2011
---------------
12/02/2011------12/03/2011 = 30
11/03/2011------11/04/2011 = 30
15/03/2011------15/07/2011 = 120
20/04/2011------16/06/2011 = 56
16/06/2011------05/08/2011 = 59
17/07/2011------18/08/2011 = 31
26/11/2011------26/12/2011 = 30

Total 356 dias (+-)

y el resultado fue 217 y 219 respectivamente

La interrugante mia de unos posts arriba era esa, si aumento la fecha 3 meses (120 dias) ¿porque l diferencia es de solo 5 y 3 dias?.

Si hago el calculo de fecha entre fecha me dá en total 264 +- con Ferbrero a 28 dias
¿Como es que me dá uno 212 y 216? ¿donde salen estos números? y no llega a 264?

[fjchavez]

mrocf no entiendo tu codigo desde el for each pero funciona perfectamente!!!!!!!!!!!!! y creo que queda generalizado el contar los dias cuando hay fechas que se traslapan o no!!
Por otro lado creo este procedimiento deberia ser elevado a Funcion!!!! se puede??, es decir:

Código:

Function cuentadias(r as range)

cuenta días = Call Macro787

end function

[mrocf]

La base del funcionamiento la puedes ver en lo que se va almacenando en las columnas AM y AN.
Si lo miras bien verás que se hace el mismo análisis que harías "a mano".

Una vez que lo hayas comprendido, podrías -efectivamente- reformular el "For Each - Next" de modo de no llevar la información a celdas sino a una matriz de dos columnas.

Naturalmente, al término del "For Each - Next", en lugar del "Evaluate" tendrás que restar la fecha de fin menos la fecha de inicio para cada fila de la matriz, e ir acumulando el resultado.

Ten en cuenta (esto es lo más importante desde lo conceptual) que desde una función personalizada no es posible manipular objetos (te recuerdo que las celdas del Excel son objetos).
Por ello no es factible un traslado "formal" (así como así) del código mostrado a una función personalizada.

Saludos y me alegra que te haya sido de utilidad.
Saludos, Cacho.

[fjchavez]

Ok esa nueva matriz que iria almacenando los datos, como hago para redimensionarla???
Estoy usado Redim Preserve pero no logro redimensionarla y que me ingrese otra fila??

[mrocf]

Mira por aquí: Instrucción ReDim.

Saludos, Cacho.

[fjchavez]

Hola mrocf,
Publico la funcion que he creado para este calculo por si alguien más se topa con esta tarea.

Funcion OrdenarMatrizNx2: Coje el rango y ordena segun fechas y lo almacena en una matriz Nx2
Funcion TDiasTraslapan: Calcula la suma total de dias (sin repetición) cuando hay fechas que se traslapan.

Código VBA:

Function OrdenarMatrizNx2(r As Range) As Variant
 
Dim ArrayName As Variant
Dim t As Variant
Dim i As Integer
Dim j As Integer
Dim y As Integer
Dim condition1 As Boolean
Dim condition2 As Boolean
Dim sortcolumn1 As Integer
Dim sortcolumn2 As Integer
 
ArrayName = r
sortcolumn1 = 1
sortcolumn2 = 2
 
For i = LBound(ArrayName, 1) To UBound(ArrayName, 1) - 1
    For j = LBound(ArrayName, 1) To UBound(ArrayName, 1) - 1
        condition1 = (ArrayName(j, sortcolumn1) > ArrayName(j + 1, sortcolumn1))
        condition2 = ((ArrayName(j, sortcolumn1) = ArrayName(j + 1, sortcolumn1)) And (ArrayName(j, sortcolumn2) > ArrayName(j + 1, sortcolumn2)))
        If condition1 Or condition2 Then
            For y = LBound(ArrayName, 2) To UBound(ArrayName, 2)
                 t = ArrayName(j, y)
                 ArrayName(j, y) = ArrayName(j + 1, y)
                 ArrayName(j + 1, y) = t
            Next y
        End If
    Next j
Next i
 
OrdenarMatrizNx2 = ArrayName
 
End Function
 
Function TDiasTraslapan(r As Range) As Integer
 
Dim a, b, d As Integer
Dim ArrayName As Variant
ReDim ArrayFechas(1 To 2, 1 To 1) As Variant
Dim dias As Long
 
Application.Volatile
 
'Trasladamos a matriz y ordenamos los datos con la funcion OrdenaMatrizNx2
ArrayName = OrdenarMatrizNx2(r)
'Asignamos la primera columna con los valores fecha inicial y fecha final de ArrayName
ArrayFechas(1, 1) = ArrayName(1, 1)
ArrayFechas(2, 1) = ArrayName(1, 2)
'Procedemos a comparar y rellenar la matriz ArrayFechas
For a = LBound(ArrayName, 1) + 1 To UBound(ArrayName, 1)
    b = LBound(ArrayName, 2)
    fila = UBound(ArrayFechas, 1)
    columna = UBound(ArrayFechas, 2)
    Select Case ArrayName(a, b) <= ArrayFechas(fila, columna)
        Case True
            If ArrayName(a, b + 1) > ArrayFechas(fila, columna) Then
                ArrayFechas(fila, columna) = ArrayName(a, b + 1)
            End If
        Case False
            ReDim Preserve ArrayFechas(1 To 2, 1 To columna + 1)
            ArrayFechas(fila - 1, columna + 1) = ArrayName(a, b)
            ArrayFechas(fila, columna + 1) = ArrayName(a, b + 1)
    End Select
Next a
        
ArrayFechas = Application.Transpose(ArrayFechas)
dias = 0
 
For d = LBound(ArrayFechas, 1) To UBound(ArrayFechas, 1)
dias = dias + CDate(ArrayFechas(d, UBound(ArrayFechas, 2))) - CDate(ArrayFechas(d, LBound(ArrayFechas, 2))) + 1
Next d
 
TDiasTraslapan = dias
 
End Function
 

Gracias por la ayuda mrocf

[fjchavez]

Una consulta mas!!!, pues sucede que la instrucción application.volatile no me recalcula bien la formula, me da otro valor, pero si voy a la celda que tiene la fórmula, doy F2 y enter.....y recien me hace el calculo correcto.
Hay alguna forma de que ni bien cambio alguna fecha, me recalcule la formula?

[rema28]

como lo puedo ejemplificar en asp.net... utilizando esas dos funciones