Excel datestamp question

Factual Questions
1

I’m helping my wife with a spreadsheet she is designing to help with her work. Most of it is pretty straightforward, but one of the things she is asking for has me stumped.

She would like one of the cells (say for example B2) to record the date that a value was entered in another cell (say D2). What she would like to happen is that whenever someone enters data in D2 that the current date will populate in B2.

I don’t consider myself an expert in Excel by any means, but I can’t think of any functions that would accomplish this. Perhaps some kind of macro would work, but I’ve never used macros before, so I’m not even sure where to start.

If any of you Excel gurus have thoughts on this I would appreciate it.

2

=now() will give you the current time, but will update everytime Excel refreshes formulas. To keep it static the user would need to copy/paste values immediately. Works fine as long as you trust the user.

As far as having it done automatically when data is entered… that seems like a strech that would require some advanced macros. Doable, but pretty advanced.

Hope this helped.

3

Pressing the semicolon key while holding control will insert the current date.

Pressing the semicolon key while holding control and shift will insert the current time.

4

Definitely requires a macro.
I recommend protecting the input and date stamp cells, allowing entry only via input box, macro would then date stamp the date stamp cell.

5

I don’t see how requiring the user to either cut and paste the date, or hold down the semicolon key is any better than just asking them to enter the date manually.

The problem is that sometimes people are entering a value in the cell, but forgetting to put in the date that the entry was done. If they can’t remember to put in a date, how are they going to remember to go to the cell and press CTRL + semicolon?

6 

Private Sub Worksheet_Change(ByVal Target As Range)
 If Intersect(Target, Range("D2")) Is Nothing Then
  Exit Sub
 Else
  Range("B2").Value = Date
   End If
End Sub
7

Yoyodyne has it. Here’s a little more generic version, for any cell in column B:


Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 2 Then
        Cells(Target.Row, Target.Column + 2) = Date
    End If
End Sub
8

Won’t that just date stamp it the first time a value is entered in a cell? How will that change the date for an updated value?

9

The code above would update the date stamp each time the value in a cell is changed, not just the first time it is entered. It wasn’t clear from the OP if this is desired. If not, you could easily add an IF statement that wouldn’t update the date if one already existed.

10

Thanks. I’ll give that a try.