Time stamp when a cell is changed

Question

Hello, I have an excel problem I am hoping someone can help with. Column A has 5 rows with a drop down list options 1,2, and 3. Column B has 5 corresponding rows with the function =IF(A1&lt;&gt;"",NOW(),"") with the “A1” changing depending on the row. I would like to the time stamp in column B to only change when I change the cell in the same row as column A. Additionally, I would like the time stamps only to change when the cell is changed and not when the file is opened/closed. Am I going about this the right way or is there a simpler way of accomplishing this task? Please let me know if this makes sense or if further explanation is needed. Thank you!

mathetes · Answer

Tanner_Ayers&nbsp;&nbsp;Unfortunately for you, NOW() means exactly what it says, NOW! And NOW! changes to whatever NOW happens to be, not based on other cell changes.&nbsp;So why don't you back up and give us the bigger picture of what your spreadsheet is capturing and why precise timing and "freezing things" in the form of a data snapshot is a crucial part of it. What does column A's containing of 1, 2 or 3 represent? What are the five rows about? Are there only five rows? Or are we (i.e., you) dealing with collections of five rows?&nbsp;&nbsp;And so forth. What is the context in which all of this takes place? What kind of output or report or account are you expecting as a result?

hansvogelaar · Answer

Tanner_Ayers&nbsp;
If you're willing to use VBA, it is possible.
Right-click the worksheet tab and select 'View Code' from the context menu.
Copy the code listed below into the worksheet module.
Switch back to Excel.
Save the workbook as a macro-enabled workbook (.xlsm).
You'll have to allow macros when you open the workbook.
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    If Not Intersect(Range("A1:A5"), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        For Each rng In Intersect(Range("A1:A5"), Target)
            If rng.Value = "" Then
                rng.Offset(0, 1).ClearContents
            Else
                rng.Offset(0, 1).Value = Now
            End If
        Next rng
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

tanner_ayers · Answer

mathetes&nbsp;Thank you for the quick response!&nbsp;The scenario I gave with list 1,2,3 is me testing the formula before using it. In column A I have a status of a project ie "developed", "started", "on hold", "finished" etc. (a total of 8 things on the list). The number of rows is continuously growing as we get new projects and not limited to 5. The goal is to output a date in another column when the status of a project is changed. I don't need previous status changes dates ie if a project is "finished" I don't care what date the cell was changed to "started" I only want the date when the cell was changed to "finished"&nbsp;

tanner_ayers · Answer

This will work however I am trying to avoid VBA due to this document being worked on by multiple users with varying levels of excel knowledge. The document is auto saved and I am afraid of the macro breaking when other people are inputting into the document.

hansvogelaar · Answer

Tanner_Ayers&nbsp;
Unfortunately that means it is not possible to do what you want.

Forum Discussion

Time stamp when a cell is changed

6 Replies

Resources