Share via

Datestamps in Excel when a cell value changes

Daniel Checkman 6 Reputation points
2022-12-20T17:21:58.08+00:00

I've already looked up adding datestamps in Excel. Not a problem. I already have a table with data in it. I don't want to display the current date if there is already data in the cell. I only want the datestamp to update when the other cell value changes, but I do want a default date for the unchanged data. So, here is the example,...

Suppose I already have a value of 1000 in cell A2 and a blank in cell A3. I want a default date in cell B2, but if cell A2 changes to 1001, I want the current date in B2. For cell B3, I want it to be a blank until data is entered/updated into B2.

How can this be done?

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,679 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ali AlEnezi 1,061 Reputation points
    2022-12-20T17:39:13.323+00:00

    Hi @Daniel Checkman

    To add a datestamp to a cell in Excel when the value in another cell changes, you can use the following formula:

    =IF(A2<>A2, "", TODAY())

    This formula will return a blank value if the value in cell A2 has not changed, and will return the current date if the value in cell A2 has changed. You can then copy and paste this formula into the other cells in column B to apply the same logic to those cells.

    Here's an example of how you could use this formula:

    In cell B2, enter the formula =IF(A2<>A2, "", TODAY()). This formula will return a blank value if the value in cell A2 has not changed, and will return the current date if the value in cell A2 has changed.

    In cell B3, enter the formula =IF(A3<>A3, "", TODAY()). This formula will return a blank value if the value in cell A3 has not changed, and will return the current date if the value in cell A3 has changed.

    Repeat this process for the other cells in column B as needed.

    Good luck!

    1 person found this answer helpful.
  2. Cimjet 81 Reputation points
    2022-12-22T21:19:41.863+00:00

    Hi Daniel
    I can do what you want if you compare column A to another column. It can be in the same sheet or another sheet
    eg:
    Column A Your Data, then copy your Data to Column E
    This way if you change anything in column A the macro will detect the difference between the two columns and stamp date and time in column B.
    This is a sample Macro that compairs column A with Column E
    Try it in a test worksheet and see if that will work for you.
    You may need to format your date and time.
    This is an Event Macro, it goes in the Worksheet not in a Module but We can change it if you like.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim cel As Variant, LastRow As Long
    LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
    For Each cel In Range("A2:A" & LastRow)
    If cel.Value <> cel.Offset(0, 5) Then
    cel.Offset(0, 1) = Now
    End If
    Next cel
    End Sub

    0 comments