** NORMAS DEL FORO **
Inicio del foro Inicio del foro > Otros de Microsoft: Windows y Office > Excel
  Mensajes nuevos Mensajes nuevos RSS - Restar fechas recientes de registros coincidentes
  Preguntas frecuentes Preguntas frecuentes  Buscar en el foro   Eventos   Registro Registro  Iniciar sesion Iniciar sesion

Tema cerradoRestar fechas recientes de registros coincidentes

 Responder Responder
Autor
Mensaje
John_Arnedo Ver desplegable
Habitual
Habitual
Avatar

Unido: 13/Febrero/2009
Localización: Colombia
Estado: Sin conexión
Puntos: 192
Enlace directo a este mensaje Tema: Restar fechas recientes de registros coincidentes
    Enviado: 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. 



Arriba
AnSanVal Ver desplegable
Administrador
Administrador
Avatar

Unido: 16/Marzo/2005
Localización: España
Estado: Sin conexión
Puntos: 5343
Enlace directo a este mensaje Enviado: 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 sitio_web con ejemplos Excel.
Arriba
John_Arnedo Ver desplegable
Habitual
Habitual
Avatar

Unido: 13/Febrero/2009
Localización: Colombia
Estado: Sin conexión
Puntos: 192
Enlace directo a este mensaje Enviado: 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. 





Editado por John_Arnedo - 04/Noviembre/2018 a las 15:57
Arriba
AnSanVal Ver desplegable
Administrador
Administrador
Avatar

Unido: 16/Marzo/2005
Localización: España
Estado: Sin conexión
Puntos: 5343
Enlace directo a este mensaje Enviado: 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 sitio_web con ejemplos Excel.
Arriba
John_Arnedo Ver desplegable
Habitual
Habitual
Avatar

Unido: 13/Febrero/2009
Localización: Colombia
Estado: Sin conexión
Puntos: 192
Enlace directo a este mensaje Enviado: 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







Arriba
AnSanVal Ver desplegable
Administrador
Administrador
Avatar

Unido: 16/Marzo/2005
Localización: España
Estado: Sin conexión
Puntos: 5343
Enlace directo a este mensaje Enviado: 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 sitio_web con ejemplos Excel.
Arriba
AnSanVal Ver desplegable
Administrador
Administrador
Avatar

Unido: 16/Marzo/2005
Localización: España
Estado: Sin conexión
Puntos: 5343
Enlace directo a este mensaje Enviado: 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 sitio_web con ejemplos Excel.
Arriba
John_Arnedo Ver desplegable
Habitual
Habitual
Avatar

Unido: 13/Febrero/2009
Localización: Colombia
Estado: Sin conexión
Puntos: 192
Enlace directo a este mensaje Enviado: 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.

Arriba
AnSanVal Ver desplegable
Administrador
Administrador
Avatar

Unido: 16/Marzo/2005
Localización: España
Estado: Sin conexión
Puntos: 5343
Enlace directo a este mensaje Enviado: 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?





Editado por AnSanVal - 05/Noviembre/2018 a las 13:52
Aprendemos viendo respuestas de otros, también intentando resolver dudas (intenta ayudar cuando puedas/sepas).

Mi sitio_web con ejemplos Excel.
Arriba
John_Arnedo Ver desplegable
Habitual
Habitual
Avatar

Unido: 13/Febrero/2009
Localización: Colombia
Estado: Sin conexión
Puntos: 192
Enlace directo a este mensaje Enviado: 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í.

Arriba
AnSanVal Ver desplegable
Administrador
Administrador
Avatar

Unido: 16/Marzo/2005
Localización: España
Estado: Sin conexión
Puntos: 5343
Enlace directo a este mensaje Enviado: 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 sitio_web con ejemplos Excel.
Arriba
John_Arnedo Ver desplegable
Habitual
Habitual
Avatar

Unido: 13/Febrero/2009
Localización: Colombia
Estado: Sin conexión
Puntos: 192
Enlace directo a este mensaje Enviado: 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



Arriba
 Responder Responder
  Compartir tema   

Ir al foro Permisos de foro Ver desplegable