Imprimir página | Cerrar ventana

Restar fechas recientes de registros coincidentes

Impreso de: Foro de Access y VBA
Categoría: Otros de Microsoft: Windows y Office
Nombre del foro: Excel
Descripción del foro: Foro de Excel y VBA de Excel
URL: http://www.mvp-access.com/foro/forum_posts.asp?TID=84052
Fecha de impresión: 23/Abril/2019 a las 02:29


Tema: Restar fechas recientes de registros coincidentes
Publicado por: John_Arnedo
Asunto: Restar fechas recientes de registros coincidentes
Fecha de publicación: 04/Noviembre/2018 a las 11:25
Hola Comunidad

No doy con la respuesta pero siento que debería ser fácil.

Tengo una tabla en una hoja de excel donde se anota cada vez que físicamente un expediente pasa de un lugar a otro. Es decir, supongamos que el expediente 2018-0001 pasó de Secretaría a Despacho entonces en un registro específico se anota esta novedad; hay un campo especial donde se anota la fecha y hay otro campo que calcula el tiempo que ha durado el expediente en ese lugar. 

Lo que quiero es calcular el número de días que estuvo el expediente en ese lugar pero con base en un registro anterior cuyo expediente coincida. Es decir que si en el ejemplo de arriba el expediente pasó de "despacho" nuevamente a "Secretaría", reste las fechas de la nueva novedad con aquella del registro coincidente. 

La tabla tiene más o menos la siguiente estructura: 
Id Expediente Ubicación Fecha novedad Dias ubicacion
1 2016-00102 DESPACHO 26/10/2018
2 2009-00314 OFICIAL MAYOR 26/10/2018
3 2009-00279 OFICIAL MAYOR 26/10/2018
4 2017-00071 SENTENCIA DE FONDO 26/10/2018
5 2016-00102 Secretaría 3/11/2018

La idea es calcular en la columna "días ubicación" la diferencia entre las fechas mas recientes de novedades. 

Téngase presente que: 

  • El ejemplo muestra 5 registros pero estoy lidiando con miiles.
  • Los registros referentes a un solo expediente pueden ser muchos.
  • Necesito que las diferencias de fechas sean entre las mas recientes coincidiendo con número del expediente 
El problema para mí del ejercicio es que estoy bloqueado, ni siquiera doy con alguna idea. 






Respuestas:
Publicado por: AnSanVal
Fecha de publicación: 04/Noviembre/2018 a las 15:06


Ten presente que con una información  basada en «mas o menos», la eficiencia de mi respuesta dependerá de  «mas o menos».



Suponiendo los datos en A1:E2000 :


- Selecciona E2  > Fórmulas > Nombres definidos > Administrador de nombres > Nuevo >...

     Nombre:   rango

     Hace referencia a:  =DESREF(Hoja1!D8;1;0;CONTAR(Hoja1!$D:$D)-FILA(Hoja1!C7);3)


...  y en [E2] escribe:    =SI.ERROR(BUSCARV(B2;rango;3;0);HOY())-D2






-------------
Aprendemos viendo respuestas de otros, también intentando resolver dudas (intenta ayudar cuando puedas/sepas).

Mi http://achinet.mvp-access.es/" rel="nofollow - sitio_web con ejemplos Excel.


Publicado por: John_Arnedo
Fecha de publicación: 04/Noviembre/2018 a las 15:49
¡Funciona! Mil gracias AnSanVval

Ahora estoy tratando de entender porqué funciona jajajajja

Bueno, en la forma editada no lo he probado, aunque parece igual solo que ahorrando líneas de fórmulas. Hasta ahora bajo =SI.ERROR(BUSCARV(B2;DESREF(B2;1;0;CONTAR($D:$D)-FILA(A1);3);3;0);HOY())-D2 ha funcionado bien

Lo cierto es que voy a aplicarlo a una tabla y excel me cambia los nombre de las columnas por el de campos y cosas así por eso tengo que exprimir esta fórmula. 





Publicado por: AnSanVal
Fecha de publicación: 04/Noviembre/2018 a las 22:00
 Es lo mismo:  =SI.ERROR(BUSCARV([@Expediente];rango;3;0);HOY())-[@[Fecha novedad]]




-------------
Aprendemos viendo respuestas de otros, también intentando resolver dudas (intenta ayudar cuando puedas/sepas).

Mi http://achinet.mvp-access.es/" rel="nofollow - sitio_web con ejemplos Excel.


Publicado por: John_Arnedo
Fecha de publicación: 04/Noviembre/2018 a las 22:28
La maestría AnSAnVAl

La formula funciona correctamente, ya la apliqué a la tabla sin mayores problemas, pero intenté que los días calculados fueran los hábiles mediante la fórmula DIAS.LAB.INTL pero no me da. 

Parece que todo cambia si intento eso y al parecer no he logrado entender la formula que me diste a cabalidad pues los argumentos de esta última función son fáciles, realmente lamento no poder avanzar mucho solo y me da hasta pena preguntar tanto









Publicado por: AnSanVal
Fecha de publicación: 04/Noviembre/2018 a las 22:42
 

«... Ahora estoy tratando de entender porqué funciona...»


Quizá lo entiendas un poco mejor si sigues estos pasos:

- Haz clic en una de las celdas  «Dias ubicación» (recuerda la que elijas) > pulsa F5 (Ir a) > escribe rango > pulsa Aceptar


Ese es el rango donde la fórmula busca una coincidencia con el expediente de la fila donde hiciste clic.





-------------
Aprendemos viendo respuestas de otros, también intentando resolver dudas (intenta ayudar cuando puedas/sepas).

Mi http://achinet.mvp-access.es/" rel="nofollow - sitio_web con ejemplos Excel.


Publicado por: AnSanVal
Fecha de publicación: 04/Noviembre/2018 a las 23:06

¿Días laborables? ...  no entiendo la relación con días de almacenaje/permanencia.


No me seducen demasiado las fórmulas muy largas, si no ves clara la anterior... esta otra...  Confused


=DIAS.LAB.INTL([@[Fecha novedad]];[@[Fecha novedad]] + SI.ERROR(BUSCARV([@Expediente];rango;3;0);HOY())-[@[Fecha novedad]];1;$N$1:$N$16)





-------------
Aprendemos viendo respuestas de otros, también intentando resolver dudas (intenta ayudar cuando puedas/sepas).

Mi http://achinet.mvp-access.es/" rel="nofollow - sitio_web con ejemplos Excel.


Publicado por: John_Arnedo
Fecha de publicación: 05/Noviembre/2018 a las 05:55
AnSanVal, que algo tuve que hacer bien en mi otra vida para encontrarme en esta gente tan colaboradora. Mil gracias 

El tema de los hábiles es porque a veces los expedientes están en "Traslado" y normalmente se cuentan días hábiles, la idea mías es condicionar todas estas formulas para que dependiendo donde esté el expediente, calcule días hábiles o calendarios. 

Estoy estudiando bien las formulas Desref para entender como manejaste lo del "Rango" porque ahí es donde estoy un poco quedado. 

La última formula me ha servido perfectamente.



Publicado por: AnSanVal
Fecha de publicación: 05/Noviembre/2018 a las 10:26

DESREF (desvio referencia) está bien explicado en la ayuda, calcula un nuevo rango en base a una celda de origen conocida. Intentaré explicarla:


Sintaxis:  =DESREF(celdaOrigen ; y ; x ; h ; w)


- celdaOrigen =  celda a partir de la cual se desvía el nuevo rango.

- y = cuantas celdas hacia abajo empieza el nuevo rango.

- x = cuantas celdas hacia la derecha empieza el nuevo rango (y   x  =  celda superior izquierda).

- h = altura del nuevo rango.

- w = ancho del nuevo rango.


En la fórmula   =DESREF(B2;1;0;CONTAR($D:$D)-FILA(A1);3)    


- celdaOrigen = B2   (referencia relativa)

- y = 1    (fila siguiente -3-)

- x = 0    (misma columna -B-)

- h = CONTAR($D:$D-FILA(A1)    (cuenta las fechas)     (FILA(A1) = 1     en la fórmula de E3 en vez de FILA(A1) al ser relativa contiene FILA(A2), etc.

- w = 3     (columnas  B:D)


Equivalencia en [E2]:   =DESREF(B2;1;0;10;3)    (10 = 11 FECHAS - 1)     busca en el rango: B3:D12 ,  que comienza en B3 y contiene 10 filas y 3 columnas.



EDITADO: ¿Tal vez me paso en detalles?





-------------
Aprendemos viendo respuestas de otros, también intentando resolver dudas (intenta ayudar cuando puedas/sepas).

Mi http://achinet.mvp-access.es/" rel="nofollow - sitio_web con ejemplos Excel.


Publicado por: John_Arnedo
Fecha de publicación: 05/Noviembre/2018 a las 15:00
Jaajjajajaa bastante didáctico, nada que objetar

Pero, quiere decir que la formula extrae los datos de forma lineal,  no busca las fechas más reciente, en otras palabras, funciona porque los datos siguen un orden lineal pero se trucaría si se introducen en desorden por alguna equivocación de alguien.

Es obvio que para lo que se quiere hacer, así debería funcionar, pero solo me asalta esa duda y quizá era lo que no entendía porque no veía en ningún momento  cuando se determinaba en la formula cuales eran las fechas mas recientes para restarse entre sí.



Publicado por: AnSanVal
Fecha de publicación: 05/Noviembre/2018 a las 20:38
Yo supongo que solamente se registrará un expediente con su nueva ubicación, fecha, etc.; cuando cambie de 'residencia' (por decirlo de un modo coloquial) y no volverá a registrarse de nuevo hasta que vuelva a cambiar nuevamente de sitio. 

No sería muy coherente (por ejemplo) que un expediente esté en Secretaría, cambie a Despacho y una semana después  regrese a Secretaría, pero que se registre primero el regreso Secretaría >> Secretaría y posteriormente se registre el cambio Secretaría >> Despacho. Estos eventos deberían hacerse cronológicamente, pues si en este ejemplo (y este momento) extraemos el último expediente registrado de ese código, nos dirá que actualmente se encuentra en Despacho, cuando no es cierto que esté allí.




-------------
Aprendemos viendo respuestas de otros, también intentando resolver dudas (intenta ayudar cuando puedas/sepas).

Mi http://achinet.mvp-access.es/" rel="nofollow - sitio_web con ejemplos Excel.


Publicado por: John_Arnedo
Fecha de publicación: 07/Noviembre/2018 a las 04:49
Sí, es cierto no tendría sentido.

¡AnSanVal, muchísimas gracias, tus formulas me han funcionado a la perfección!

Se puede cerrar el hilo






Imprimir página | Cerrar ventana