Forum Discussion

Joseph Huisman's avatar
Joseph Huisman
Copper Contributor
Oct 01, 2018

Automatically adding the date/time to a cell when another cell is updated

I am trying to create a spreadsheet where when a specific cell is updated in anyway, the date/time stamp automatically updates in the cell right beneath the cell that was updated. I only need to have...
excel
Haytham Amairah's avatar
Haytham Amairah
Silver Contributor
Oct 01, 2018

Hi Joseph,

 

This is possible, but you definitely need to a VBA code to do it!

 

Let's say the five cells you want to target are from cell A1 to E1, so please hover the Mouse over the Worksheet tab, right-click, and select View Code.

 

Then copy and paste this code into the worksheet code module:

Sub Worksheet_Change(ByVal Target As Range)

 If Not Intersect(Target, Range("A1")) Is Nothing Or _
    Not Intersect(Target, Range("B1")) Is Nothing Or _
    Not Intersect(Target, Range("C1")) Is Nothing Or _
    Not Intersect(Target, Range("D1")) Is Nothing Or _
    Not Intersect(Target, Range("E1")) Is Nothing Then

    Target.Offset(1, 0) = Now
 End If

End Sub

 

After that, save the workbook as .xlsm file extension to keep the code saved in it.

If you want to apply it to different cells, simply, you can change the cell ranges in the code and their order in the code does not matter!.

 

Hope that helps

Joseph Huisman's avatar
Joseph Huisman
Copper Contributor
Oct 01, 2018

Haytham - Thank you so much for the quick response on this. This works perfectly and I can see that changing the target offset allows me to change the location of the timestamp. 

This is great and exactly what I needed. I really appreciate the help. 

 

  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    Oct 01, 2018

    Thank you, but I have discovered a simplified version of the code as follows:

    Sub Worksheet_Change(ByVal Target As Range)

     If Not Intersect(Target, Range("A1,B1,C1,D1,E1")) Is Nothing Then
        Target.Offset(1, 0) = Now
     End If

    End Sub

     

    It works the same as the previous one, so I recommend you to use it instead.

     

    Regards

    • EliasD's avatar
      EliasD
      Copper Contributor
      Feb 07, 2025

      Hello Haytham, I know this thread is quite old. And I got this code to work perfectly in my MacBook excel. However, when I increase the Range from ("B2:B10000, through, BK2:BK10000") then for some reason it stops working. Even when reducing it back down to only be 99 lines per column it breaks. Any idea why?

    • MedinaJ78's avatar
      MedinaJ78
      Copper Contributor
      Jan 18, 2022

      Haytham Amairah 
      Any other good sources using VBA, this thread looks like it ended up saying the opposite of you and died. But this is exactly what I need. I could use some help on what to search for to help find what I need.
      Thanks.

      • kovesp's avatar
        kovesp
        Copper Contributor
        Jan 19, 2022

          

        =IF(ISBLANK(C1)," ",IF(ISBLANK(B1)," ",NOW()))

         

        The current date is to be automatically inserted into B1 when C1 becomes non-blank.

        For this to work, one needs to set Enable iterative calculation on the Formulas page of Options.

        However ... 

        • This used to work in the Android version of Excel when using the smae workbook, but Microsoft, in their wisdom, "fixed"it at some point and there is no option for enabling iterative calculation in the Android version.
        • Worse, when the workbook is then opened in Windows, the option is turned off, hence the formula stops working. 
        • The option remains set, as long as the workbook is not touched on Android.

           

         

        When I added this macro to the workbook

         

        Private Sub Workbook_Open()
           Application.Iteration = True
        End Sub
         

        and saved it as XLSM (as required) it started working again. It now also works on Android, despite showing a message that the Android version doesn't support VBA.

        Ah, the mysterious ways of Microsoft ... 

         

        Anyway ... thanks for asking the question: I didn't think of trying the above before now.

         

         

         

         

         

         

         

    • Jin_Tang's avatar
      Jin_Tang
      Copper Contributor
      Jul 06, 2021
      Hi, I tried using this code, and the first code mentioned. However, it doesn't seem to be working for me. regardless of what i enter in A1/B1/C1/D1/E1, there seems to be no output in A2/B2/C2/D2/E2.

      Is there any settings that i should enable before writing this vba?
      • kovesp's avatar
        kovesp
        Copper Contributor
        Sep 04, 2021

        No VBA needed; and that would not work when the workbook is opened in the browser or mobile platforms.

         

        If the column where you want to automatically set the current date is say B, the column where the data is being entered is say C, and you are starting from B1 then therre are two variants:

        • If you want the date to update whenever the data is changed in column C, then place
             =IF(ISBLANK(C1)," ",NOW())
          in B1 and copy down.
        • If you want the date to be set only when the data in column C is changed from blank to a non-blank value (but not when the value is changed from non-black value to another non-blank value) then place
              =IF(ISBLANK(C1)," ",IF(ISBLANK(B1)," ",NOW()))
          in B1 and copy down.

        These work when the workbook is opened from Windows or Android (so it probably also works on IOS, but I don't use that platform, so can't verify).

         

        Note: There was a previous reply which was similar to the first option above, but used TODAY(). That won't work, because the TODAY() function updates whenever the workbook is opened. NOW() will not do that, it "freezes" the date when invoked.

Resources