Forum Discussion

Christine_Konow's avatar
Christine_Konow
Copper Contributor
Feb 09, 2022

Excel Formula for Date and Time

Good Morning!! I need a cell to show the date and time that another cell was updated. I know how to use the =NOW() formula, but how do you get it to update everytime the adjacent cell is updated? See my below comment for an example. Thanks:)

 

Formulas and Functions

2 Replies

  • Christine_Konow's avatar
    Christine_Konow
    Copper Contributor
    Feb 09, 2022
    What i meant to say was that i am trying to get a date and time to show up in a cell when another cell is updated. For example if I input data into B6, i want C6 to show the date and time that data had been entered into B6. Specifically for unit sign-in and sign-out. Thank you for any help you can give:)
    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Feb 09, 2022

      Christine_Konow 

      You need VBA for that.

      Right-click the sheet tab.

      Select 'View Code' from the context menu.

      Copy the following code into the worksheet module.

      Change the range as needed.

      Switch back to Excel.

      Save the workbook as a macro-enabled workbook (*.xlsm).

      Make sure that you allow macros when you open it.

      Private Sub Worksheet_Change(ByVal Target As Range)
    Dim MyRange As Range
    Set MyRange = Range("B2:B100")
    If Not Intersect(MyRange, Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Intersect(MyRange, Target).Offset(0, 1).Value = Now
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

Resources