Excel time/date stamp of a cell change

S

SemiAuto40

New Member
Joined
Feb 17, 2012
Messages
2
Hey All, This must be one of the most common questions in Excel but all of the "solutions" that I have found while searching do not work (or I am not implementing properly).

This is the problem: once a value changes in a cell on Sheet1, I need the corresponding cell in Sheet2 to get the Now() value when the cell on Sheet1 was changed. Ex: Sheet1!E6 gets a value - then Sheet2!E6 shows something like 2/17/12 8:45:33.

I want this to be generic enough to apply to a range of cells, not just a one time event.... and such that all of the cells in Sheet2 holding the Now() value don't all display the exact same time like my failed attempts do.

If VB is involved I need specific instructions on where to put in and please phrase it in a 4th grade level.;)

Please:confused: My sanity is at stake here.
 
Last edited:

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
Welcome to the Board!

Yes, this definitely requires VBA, specifically, an Event Procedure that is automatically fired when a cell is updated.

Go to Sheet1 and right click on the sheet tab name at the bottom of your screen. Select the View Code option, and paste this code into your VB window.
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
 
    Dim myAddress As String
 
'   Only run when a single cell is updated
    If Target.Count = 1 Then
'   Capture the address of the cell being updated
        myAddress = Target.Address
'   Add the date/time stamp to the corresonding cell on sheet 2
        Sheets("Sheet2").Range(myAddress) = Now()
    End If
 
End Sub
Now, as long as Macros are enabled in your workbook, whenever a single cell is updated on Sheet 1, the corresponding cell on Sheet 2 will record the date/time stamp.
 
Upvote 0
JOE4... first of all... in one word... WOW! It is not often that I get wowed!

For the hour++ that I spent trying to find the solution to this one Excel question/problem and the additional time spent trying to implement each one of the "solutions" that were offered only to find none did exactly what I needed --- Your exact solution worked perfectly AND was the most elegant in it's simplicity and was comprised of only the minimal amount of commented code to do the job! Again... WOW! I was worried when I saw the enormous number of postings to this board and just knew mine would be so far down the current latest postings that I would surely not get an answer because it rapidly got pushed down the cue by the volume of questions. I can forget those other boards that I had to register for in order to look at the proposed answer that I was looking for only to find out how little useful that I got as a result of it. My wasted time with other boards will have to be the price I paid yesterday... but now I've found THE board on Excel topics and Mr. Excel is the perfect name for this board!
Thanks to Google for pointing me to you.
 
Upvote 0
Glad we were able to help out!

The topics on this board do move pretty quickly, but many of us scan all unanswered posts from the past 24 hours to find questions that might have slipped through the cracks. Unfortunately, we are not always able to answer all the questions, but we sure answer a lot!
 
Upvote 0
hello, is it possible to time/date stamp when a cell change to a specific value?(Completed)
thanks!
 
Upvote 0
Welcome to the Board!

Sure, just add the condition to the IF statement, i.e.
change:
Code: 
If Target.Count = 1 Then
to:
Code: 
If (Target.Count = 1) and (Target = "Completed") Then
 
Upvote 0
Hello,

I would like to add time/date stamp on sheet 1, cell E7 only when any of the cells are modified. Can this be done?
 
Last edited:
Upvote 0
I would like to add time/date stamp on sheet 1, cell E7 only when any of the cells are modified. Can this be done?
Click to expand...
That's pretty easy. We can basically remove all the criteria, as we don't need to check which cells are being updated.
We just need to add code to disable events while the code is running, so the change we are making in E7 doesn't call the code itself, and we cut stuck in an infinite loop.
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    
    Application.EnableEvents = False
    Range("E7") = Now()
    Application.EnableEvents = True
    
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question
Command Button to change a Cell
Replies
4
Views
150
kevin9999
K
C
  • Solved
Count number of entries that have a date stamp of last week
Replies
6
Views
435
Joe4
Joe4
T
  • Question
Post time when cell changes, or leave it the same if not blank
Replies
1
Views
90
Joe4
Joe4
D
  • Question
VBA + Excel or Webpage Screenshot along with DateTime stamp from task bar
Replies
0
Views
161
DDRO
D
D
  • Solved
Time stamp using VBA
Replies
5
Views
916
data808
D

Forum statistics

Threads
1,214,978
Messages
6,122,547
Members
449,089
Latest member
davidcom

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top