Registrar entradas con fórmulas – una aclaración

En la nota sobre registro de entradas con fórmulas en Excel, sugerí el uso de Tablas para evitar tener que copiar las fórmulas con cada entrada.

Sin embargo, y como me señalan varios de mis lectores, el uso de tablas en este caso puede dar resultados inesperados. Veamos:

Al introducir el segundo nombre nos hemos movido con las flechas y el resultado es 0, que por el formato fecha-hora vemos como 00/01/1900 00:00:00.

Sin embargo, si nos movemos con la tecla TAB veremos que el resultado es el correcto:

En conclusión: podemos usar un rango común de Excel, copiando las fórmulas o convertir el rango en Tabla y movernos dentro de esta con la tecla TAB.

Registrar fecha y hora de una entrada en Excel con eventos

En la nota anterior vimos como registrar la fecha y hora de una entrada (timestamp) usando fórmulas.

Por supuesto puede hacerse también con macros, más precisamente, programando un evento.

El código del evento, que ponemos en el módulo de la hoja correspondiente, es el siguiente:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngColumnaNombre As Range
   
    Set rngColumnaNombre = Range("A:A")
   
    If Union(Target, rngColumnaNombre).Address = rngColumnaNombre.Address Then
        Select Case Len(Target)
            Case Is > 0
                Target.Offset(0, 1) = Now
            Case Else
                Target.Offset(0, 1).ClearContents
        End Select
    End If
      
End Sub

En nuestro ejemplo la columna A contiene los nombres y la columna B el registro de la fecha. Si borramos un nombre, también la fecha es eliminada.

El código evalúa la celda activa cuando se produce un cambio en la hoja; si la celda activa está en la columna A y no esta vacía, se registra la fecha y hora en la celda paralela de la columna B.

En este código definimos una variable de tipo rango

Set rngColumnaNombre = Range("TablaNombres").Columns(1)

La variable se refiere a la primera columna de la tabla “TablaNombres”

Esto nos permite crear una rango dinámico de manera que valores introducidos fuera de la tabla no disparan el evento.

Existen, por supuesto, otras formas de crear rangos dinámicos en el código.

1 – crear un nombre que se refiera al rango dinámico usando una fórmula con las funciones DESREF o INDICE; por ejemplo

=Hoja1!$A$2:INDICE(Hoja1!$A:$A,CONTARA(Hoja1!$B:$B)+1)

El código es

Private Sub Worksheet_Change(ByVal Target As Range)
   
    If Union(Target, Range("TablaNombres")).Address = Range("TablaNombres").Address Then
        Select Case Len(Target)
            Case Is > 0
                Target.Offset(0, 1) = Now
            Case Else
                Target.Offset(0, 1).ClearContents
        End Select
    End If
      
End Sub

2 – definir el rango en el código

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim TablaNombres As Range
   
    Set TablaNombres = Range(Range("A2"), Cells(Range("A2").End(xlDown).Row + 1, 1))
    If Union(Target, TablaNombres).Address = TablaNombres.Address Then
        Select Case Len(Target)
            Case Is > 0
                Target.Offset(0, 1) = Now
            Case Else
                Target.Offset(0, 1).ClearContents
        End Select
    End If
      
End Sub

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

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).