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).
EXCELENTE!. Felicitaciones , como siempre.
ResponderBorrarQué bueno.
ResponderBorrarGracias
La fórmula para AHORA() en ingles es NOW().
ResponderBorrarHay 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.
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.
ResponderBorrarUn saludo y hasta pronto,.
que interesante !! cada post una nueva idea :)
ResponderBorrarRoger, ¿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?
ResponderBorrarLa nota sobre la solución con un evento se publicará entre mañana y pasado mañana.
Jorge: He probado la fórmula con b2="" y en todos los valores sale: 00/01/1900 12:00:00 a.m.
ResponderBorrarEn 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.
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
ResponderBorrarBuenas, 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
ResponderBorrarespero puedas ayudarme, gracias.
Te sugiero que me mandes el archvio por mail privado (fijate en el enlace Ayuda en la parte superior de la plantilla)
ResponderBorrarRoger, fijate en la aclaración que acabo de publicar.
ResponderBorrarCon la combinación de teclas "Control" + "Bloq.Mayús + "," (la tecla de la coma) se obtiene la fecha y queda fija.
ResponderBorrarHola 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!
ResponderBorrarEn 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.
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.
ResponderBorrarSi esto no resuelve el problema puedes mandarme el archivo (fijate en el enlace Ayuda en la parte superior de la plantilla).
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
ResponderBorrarSin 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.
ResponderBorrarSegú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:
ResponderBorrar=SI(B2=0;SI(A2="";0;AHORA());B2)
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.
ResponderBorrar¿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 "".
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.
ResponderBorrarPor 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.
Gracias men, me ha funcionado nitido!
ResponderBorrarLa 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.
ResponderBorrarPor 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.
Por supuesto que es de interés. Gracias por compartir.
ResponderBorrarJorge, gracias por tu blog! Me has salvado!!! Genial!!!
ResponderBorrarGracias y adelante
ResponderBorrarRafael Palacios Velasco:
ResponderBorrarUsando 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?
Hola Héctor,
ResponderBorrarno, 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).
Buenas tardes;
ResponderBorrarme esta pasando el mismo problema de Hector Tessari, cada vez que inserto un registro se cambio la fecha y hora por la actual.
Asegurate que las rfereencias de las celdas sean las correctas.
ResponderBorrarHola¡¡ prueben con esta formula =SI(A3<>"";SI(B3<>"";B3;HOY());""), es la misma formula pero en la segunda condicionante cambie el resultado que arroja si es verdadero por el falso, y eso resolvió el problema de que no borre fecha si la casilla de la prueba lógica (o sea "A3") esta vacía. espero les sirva suerte.
ResponderBorrarLeí las fórmulas que colocaron e hice una pequeña variación para un uso que creo les puede servir a varios. Si tienen una columna de control con una lista de estatus (Ej. Listo, Notificado, Despachado - columna A). Y tienen varias columnas con fechas de control que no quieren que cambien una vez haya pasado su etapa; use la siguiente fórmula:
ResponderBorrarColumna "Fecha Listo" = SI(B2=0,SI($A2="Listo",AHORA(),0),B2)
Columna "Fecha Notificado"= =SI(C2=0,SI($A2="Notificado",AHORA(),0),C2)
Columna "Fecha Despachado" =SI(D2=0,SI($A2="Despachado",AHORA(),0),D2)
Lo único a considerar es que la columna A tiene que estar en blanco antes que escriban las fórmulas. Espero les sea útil este tip.
MUY BUENO, HASTA FUNCIONA CON HOJA DE CALCULO DE GOOGLE, CONFIGURANDO LAS OPCIONES
ResponderBorrarHOLA, como hiciste que funcionara con la hoja de google, yo no puedo :(
BorrarAunque este hilo ya tiene unos cuantos años, me sigue pareciendo muy interesante (como todo lo que divulga JLD). Recientemente he creado un vídeo explicando las dos opciones que mencioné en un comentario. Les dejo aquí el enlace, por si les resulta de interés:
ResponderBorrarEn este vídeo se explica cómo construir una fórmula que calcule el instante en el que se registra cualquier dato en una celda (timestamp), sin que se vuelva a recalcular, es decir, haciendo el resultado definitivamente estable, y todo ello sin usar macros ni Visual Basic (VBA), únicamente apoyándose en referencias circulares y cálculo iterativo: https://youtu.be/icups7cslUs
EL VIDEO APARENTEMENTE NO EXISTE
ResponderBorrarSi te referis al video aportado por Rafa (el comentario anterior al tuyo), la dirección figura al final del comentario ( https://youtu.be/icups7cslUs ).
BorrarLa probé y funciona.
te la rifaste!!! Yo lo había intentado de otras formas y con cualquier cambio en la hoja se volvía a calcular.
ResponderBorrarLO MEJOR DE LO MEJOR FELICITACIONES
ResponderBorrar