Registrar fecha y hora de una entrada en Excel con fórmulas

martes, octubre 23, 2012

Poniéndome al día con los 196 mails acumulados durante mis vacaciones, me encuentro con esta consulta:

quiero capturar la hora y la fecha del momento que ingresar este información, la función HOY() y AHORA() no sirven por que estas se actualizan cada vez que ingreso datos en la hoja; es necesario para mi que estas queden estáticas para realizar posteriores cálculos y estadísticas con ellas…

Mi primera respuesta fue que la única forma de hacerlo es programando un evento (macro). Pero después de investigar un poco el tema descubrí (y muchos otros antes de mi) que puede hacerse con fórmulas.

Para hacerlo con fórmulas tenemos que crear una referencia circular, tema mencionado tangencialmente en la prehistoria de este blog en esta nota.

En pocas palabras, ¿qué es una referencia circular? Cuando una fórmula incluye una referencia a la misma celda que la contiene o una celda que se refiere a ésta, Excel genera una advertencia de "referencia circular".

En este ejemplo, queremos calcular la ganancia neta que incluye el pago de comisiones que a su vez son calculadas en base a la ganancia neta, creándose así una referencia circular (la celda B3 contiene la fórmula =B4*15% y la celda B4 la fórmula =B1-B2-B3 que se refiere a la celda B3)








Al apretar Aceptar veremos



Para que Excel pueda resolver el cálculo tenemos que habilitar el cálculo iterativo en Opciones de Excel-Fórmulas


Después de habilitar el cálculo iterativo Excel muestra el resultado


Una vez definido el cálculo iterativo podemos usar esta fórmula para crear una registro de fecha y hora ("timestamp" en inglés)

=SI(A2<>"",SI(B2="",AHORA(),B2),"")



Para evitar tener que copiar la fórmula a lo largo de la columna A, transformamos el rango en Tabla (en un rango definido como Tabla las fórmulas y formato de las columnas son copiadas automáticamente)



26/10/2012 - Aclaración importante sobre el uso de Tablas en este modelo.

En la próxima nota veremos como hacerlo programando un evento (macros).

26 comments:

Jose Riu,  23 octubre, 2012 15:02  

EXCELENTE!. Felicitaciones , como siempre.

Roger Guerrero 23 octubre, 2012 21:00  

La fórmula para AHORA() en ingles es NOW().
Hay un error en la fórmula, debe ser: =IF(A2<>"",IF(B2<>"",NOW(),B2),"").
Por otro lado, se pierde la fecha de ingreso, pues se actualiza en cada recálculo o ingreso de nuevo dato. En ese caso se debe habilitar el cálculo manual, nunca presionar F9, y que la hoja no se recalcule al grabar.
Considero mejor crear un macro evento.

Natxo G 23 octubre, 2012 21:17  

Muchísimas gracias, Jorge. Aprecio especialmente esta sugerencia porque es una de esas ideas que no se me hubieran ocurrido ni en un millón de años. No es tanto la solución al problema en sí como la apertura a nuevas formas de pensar cuando utilizo Excel.

Un saludo y hasta pronto,.

acangros 23 octubre, 2012 23:21  

que interesante !! cada post una nueva idea :)

Jorge L. Dunkelman 23 octubre, 2012 23:46  

Roger, ¿dónde está el error? En la versión española de Excel se usa AHORA(), no NOW(). Además no se pierde la fecha de ingreso, tal como está explicado en la nota y demostrado en la animación. ¿Puedes ser un poco más explícito?
La nota sobre la solución con un evento se publicará entre mañana y pasado mañana.

Roger Guerrero 24 octubre, 2012 02:51  

Jorge: He probado la fórmula con b2="" y en todos los valores sale: 00/01/1900 12:00:00 a.m.
En cambio con b2<>"" obtengo la hora y fecha del ingreso. Solo que cuando ingreso otro valor, la celda anterior actualiza la fecha (por el recálculo automático), quedando ambas iguales a la fecha y hora actual. Por eso necesité activar el cálculo manual.
De repente estoy haciendo algo mal.

Jorge L. Dunkelman 24 octubre, 2012 06:45  

Roger, pareciera ser que no has activado el cáclculo iterativo. Por eso el resuultado es 0 que con formato de fecha-hora se ve 00/01/1900 00:00

Anónimo,  24 octubre, 2012 22:08  

Buenas, no me aparece el resultado que indicas en el ejemplo, tengo cliqueado el cálculo iterativo y lo que me aparece es cómo sigue: 0-1-00 0:00

espero puedas ayudarme, gracias.

Jorge L. Dunkelman 25 octubre, 2012 07:14  

Te sugiero que me mandes el archvio por mail privado (fijate en el enlace Ayuda en la parte superior de la plantilla)

Jorge L. Dunkelman 26 octubre, 2012 13:30  

Roger, fijate en la aclaración que acabo de publicar.

Anónimo,  29 octubre, 2012 19:20  

Con la combinación de teclas "Control" + "Bloq.Mayús + "," (la tecla de la coma) se obtiene la fecha y queda fija.

Drakael,  24 enero, 2013 22:06  

Hola Jorge, antes que nada quiero felicitarte por este blog que me ha sido de mucha utilidad para resolver entuertos de mis trabajos de excel... ¡Eres un genio!

En cuanto a esta formula, quiero decir que funciona perfectamente, al principio me daba el resultado 0. Eliminé las entradas y al volver a ingresar se corrigio la fecha/hora, solo que ahora tengo otro problema. Si vuelvo a cambiar los valores, la fecha se mantiene igual que la primera vez, por lo que no me registra si entre la primera captura y n cantidad de veces posteriores ha habido algun cambio... la primer fecha sigue igual. ¿Existe alguna solución que no implique usar macros? De antemano te agradezco tu atención.

Jorge L. Dunkelman 24 enero, 2013 22:10  

Pareciera ser que tu cuaderno tiene definidas la opciones de cálculo como "manual". Para forzar el recálculo hay que apretar F9. Fijate en la pestaña Formulas, en el grupo Cálculo-Opciones de cálculo.
Si esto no resuelve el problema puedes mandarme el archivo (fijate en el enlace Ayuda en la parte superior de la plantilla).

Anónimo,  19 diciembre, 2013 09:46  

Excelente aporte Jorge, ahora solo tengo una duda, al activar el calculo interativo, este aplica para todos mis archivos de excel, y yo solo necesito que sea en esta sola hoja, ya que me causa conflicto con mis demas archivos, ¿habra alguna otra manera de hacer que se ponga la hora y fecha al ingresar un dato sin activar el calculo interativo y sin macros? De antemano gracias y exelente aporte saludos

Jorge Dunkelman 19 diciembre, 2013 14:03  

Sin cálculo iterativo no se puede hacer. Excel tiene la propiedad de transferir las definiciones del primer cuaderno que abras a los restantes. Así que las alternativas son que el cuaderno en cuestión no sea el primero que abras en cada sesión, o que despues de abrir otro cuaderno cambies las definiciones en Opciones-Fórmula.

Rafael Palacios Velasco 01 abril, 2014 01:16  

Según observo, la fórmula propuesta "borra" el resultado de B2 si se borra el dato de A2. Es decir, es permanente mientras el dato de A2 permanezca. Si lo que se quiere es que el instante de registro siga invariable, es decir, que una vez registrado no vuelva a modificarse la fecha y hora de registro, la fórmula debe ser:

=SI(B2=0;SI(A2="";0;AHORA());B2)

Jorge Dunkelman 01 abril, 2014 07:28  

Hola Rafael, ¿cuál sería la lógica de dejar el registro de la fecha y hora si borramos el dato en la columna A? Al borrar el dato en A (el nombre de la persona en nuestro ejemplo) el "timestamp" deja de tener sentido. La intención de la fórmula es que la fecha desaparezca cuando no hay registro en A.

¿Has probado tu fórmula? Al introducir la fórmula en la celda B2, por ejemplo, donde A2 está vacía, el resutado de la fórmula es un texto de 0 caracteres (la expresión ""). Ahora, cuando introduces un nombre en A2, la fórmula evalúa primero si B2=0, lo cual siempre será Falso, por lo que ahora evaluará si A2="", lo cual tampoco es Verdadero, por lo que el resultado será siempre "".

Rafael Palacios Velasco 15 abril, 2014 12:10  

La razón de mantener la datación del registro pese al borrado del dato es saber cuándo se manipuló la celda originalmente. Con fines de supervisión, en algunas hojas de cálculo necesito saber en qué momento se anotó algo en una celda por primera vez, sin perder esa información en caso de que posteriormente se borre o se modifique el dato.

Por supuesto, esto no será lo que se necesite en la mayoría de los casos, de modo que mi fórmula no pretende corregir la suya, sino proporcionar una alternativa para este caso que le describo. En resumen, si se quiere que el timestamp desaparezca al borrar el dato, la fórmula idónea sería =SI(A2<>"",SI(B2="",AHORA(),B2),""), pero cuando se quiera conservar el timestamp aunque el dato original desaparezca, entonces una alternativa sería =SI(B2=0;SI(A2="";0;AHORA());B2).

He comprobado mi fórmula y da el resultado esperado. Si la celda donde se espera el registro es A2 y está inicialmente vacía, al introducir la fórmula propuesta en B2, cuyo valor es inicialmente nulo, se evaluará el condicional interior (el segundo =SI), cuyo resultado será otra vez nulo, pues A2 está vacía. Mientras A2 esté vacía (y no haya dejado de estarlo), la fórmula propuesta siempre dará un resultado nulo. Cuando se registre algo en A2, el condicional interior dejará de ser cierto, porque A2 ya no estará vacía, y el resultado de la fórmula ya será =AHORA(). B2 deja de ser nulo en esta iteración así que, en lo sucesivo, el primer condicional siempre se evaluará como falso, devolviendo el propio valor de B2, que nunca más cambiará. Si posteriormente se borra el dato introducido en A2 y esta celda vuelva a estar vacía, B2 seguirá devolviendo la fecha del primer registro, porque la evaluación del primer condicional (que será falso) impide que llegue a evaluarse el segundo (que sí sería verdadero, pero no llega a operar).

Uso esta fórmula con eficacia en el caso descrito más arriba, pero con un par de matices. El primero es que da lugar a una referencia circular, por lo que debe activarse el cálculo de libro automático y habilitar el cálculo iterativo en la sección Fórmulas de las Opciones de Excel. El segundo matiz es que la fórmula arroja como resultado un cero mientras esté “latente”, en espera de algún movimiento, es decir, mientras en A2 no se haya introducido nada nunca (no basta con que esté vacía, sino que se requiere que nunca haya dejado de estarlo). Este resultado, por tratarse de una fecha, se interpretará como las 0:00 horas del 1 de enero de 1900, lo que puede carecer de sentido si se emplea este resultado en otros cálculos. Por otro lado, como dicha fecha no es real, prefiero que no sea visible, lo que consigo con un formato condicional que lo haga “invisible” sin evitar que la fórmula actúe.

Espero que esta aclaración pueda ser de interés para sus seguidores y aprovecho para felicitarle por su extraordinario blog, del que muchos obtenemos un valioso provecho.

Miguel Constanza 16 abril, 2014 02:11  

Gracias men, me ha funcionado nitido!

Rafael Palacios Velasco 16 abril, 2014 12:56  

La razón de mantener la datación del registro pese al borrado del dato es saber cuándo se manipuló la celda originalmente. Con fines de supervisión, en algunas hojas de cálculo necesito saber en qué momento se anotó algo en una celda por primera vez, sin perder esa información en caso de que posteriormente se borre o se modifique el dato.

Por supuesto, esto no será lo que se necesite en la mayoría de los casos, de modo que mi fórmula no pretende corregir la suya, sino proporcionar una alternativa para este caso que le describo. En resumen, si se quiere que el timestamp desaparezca al borrar el dato, la fórmula idónea sería =SI(A2<>"",SI(B2="",AHORA(),B2),""), pero cuando se quiera conservar el timestamp aunque el dato original desaparezca, entonces una alternativa sería =SI(B2=0;SI(A2="";0;AHORA());B2).

He comprobado mi fórmula y da el resultado esperado. Si la celda donde se espera el registro es A2 y está inicialmente vacía, al introducir la fórmula propuesta en B2, cuyo valor es inicialmente nulo, se evaluará el condicional interior (el segundo =SI), cuyo resultado será otra vez nulo, pues A2 está vacía. Mientras A2 esté vacía (y no haya dejado de estarlo), la fórmula propuesta siempre dará un resultado nulo. Cuando se registre algo en A2, el condicional interior dejará de ser cierto, porque A2 ya no estará vacía, y el resultado de la fórmula ya será =AHORA(). B2 deja de ser nulo en esta iteración así que, en lo sucesivo, el primer condicional siempre se evaluará como falso, devolviendo el propio valor de B2, que nunca más cambiará. Si posteriormente se borra el dato introducido en A2 y esta celda vuelva a estar vacía, B2 seguirá devolviendo la fecha del primer registro, porque la evaluación del primer condicional (que será falso) impide que llegue a evaluarse el segundo (que sí sería verdadero, pero no llega a operar).

Uso esta fórmula con eficacia en el caso descrito más arriba, pero con un par de matices. El primero es que da lugar a una referencia circular, por lo que debe activarse el cálculo de libro automático y habilitar el cálculo iterativo en la sección Fórmulas de las Opciones de Excel. El segundo matiz es que la fórmula arroja como resultado un cero mientras esté “latente”, en espera de algún movimiento, es decir, mientras en A2 no se haya introducido nada nunca (no basta con que esté vacía, sino que se requiere que nunca haya dejado de estarlo). Este resultado, por tratarse de una fecha, se interpretará como las 0:00 horas del 1 de enero de 1900, lo que puede carecer de sentido si se emplea este resultado en otros cálculos. Por otro lado, como dicha fecha no es real, prefiero que no sea visible, lo que consigo con un formato condicional que lo haga “invisible” sin evitar que la fórmula actúe.

Espero que esta aclaración pueda ser de interés para sus seguidores y aprovecho para felicitarle por su extraordinario blog, del que muchos obtenemos un valioso provecho.

Jorge Dunkelman 16 abril, 2014 14:47  

Por supuesto que es de interés. Gracias por compartir.

Saluti 30 mayo, 2014 10:43  

Jorge, gracias por tu blog! Me has salvado!!! Genial!!!

Hector Tessari 06 julio, 2015 18:24  

Rafael Palacios Velasco:

Usando tu formula para mantener la fecha a pesar de modificar me ha funcionado bien, pero al usarla en varias filas, cuando modifico el valor de la celda a controlar en la primera, también se cambian algunas de otras filas de manera aleatoria.

¿Te ha sucedido?

Jorge Dunkelman 07 julio, 2015 06:59  

Hola Héctor,
no, no me sucedido como puedes ver en la animación al final de la nota. Te sugiero que me envíes tu cuaderno para que pueda ver donde está el problema (fijate en el enlace Ayuda, en la parte superior del blog).

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP