Time stamp when a cell is changed

New Contributor

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<>"",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!

6 Replies

@Tanner_Ayers 

 

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.

 

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? 

 

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?

@Tanner_Ayers 

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

@mathetes 

Thank you for the quick response!

 

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" 

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.

@Tanner_Ayers 

Unfortunately that means it is not possible to do what you want.

@Tanner_Ayers 

 

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" 

 

If I understand now what you're doing, my suggestion would be that you not use either NOW() or TODAY() as the way to enter dates; rather just use Data Validation to ensure that a date is entered, and use the prompt to specify or describe the correct way to do it. (Presumably something like 10/18/22 or dd/mm/yy)

 

It's more conventional with databases like this to have the date as the first column in the table, and then the various entries that correspond to what has happened on that day for that project. Without seeing a sample of your actual database, at a minimum I'd recommend something like this as the column headings:

Date ---- Project --- Action/Status --- (Name of person entering) --- Other1 --- Other 2 ---

Excel has wonderful abilities to take a well designed database and extract (on separate "Dashboard" sheets) a current snapshots of (for example)

  • status of every ongoing project
  • details on any given project

The trick is to make sure that database includes the data on which you'd want to create those summary reports, and then to let Excel "do the heavy lifting" of extracting the meaningful data.

 

Is it possible to post a copy of your current workbook? If you can't do it here in the forum, put a copy on OneDrive or in GoogleDrive, and post a link here granting access. (Just make sure there's no proprietary or confidential information in it.) Then I or somebody else could offer specific suggestions on how to track these projects most effectively.