MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Date stamp

Posted by Debbie on December 20, 2001 8:42 AM

I have a spreadsheet which multiple people are working on throughout the month. I want to date stamp (mm/dd/yyyy) everytime a particular cell is updated. Is this possible. I can't seem to figure out how to do this.

Posted by Mark W. on December 20, 2001 8:46 AM

Have you considered Sharing and Track Changes? (nt)

Posted by Damon Ostrander on December 20, 2001 9:13 AM

Hi Debbie,

You didn't mention what you mean by a date stamp. Do you want a date stamp to show up in a particular cell on the spreadsheet? Or perhaps in a place in the workbook that is hidden from view? Well, whichever it is, the basis of the solution is to use the worksheet's change event to monitor when the cell of interest has been changed, and to update the date stamp then. Here's an example:

Say that you want to keep the date stamp in a custom document property that you create named "A1 Date Stamp". (This would have been created by File -> Properties -> Custom tab -> define a new document property of type Date). To update this property each time cell A1 is edited, add this code to the worksheet's event code area (right-click on the worksheet's tab, select View Code..., paste code into code pane).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, [a1]) Is Nothing Then
ThisWorkbook.CustomDocumentProperties("A1 Date Stamp").Value = Date
End If
End Sub

Of course, the reference to [a1] can be changed to any other cell reference, or to Range("MyCell") to reference a cell named "MyCell".

Just as an aside, it is also quite easy to keep a running record of when a particular cell was changed and by whom, since the user's name is stored in an Excel application property.

Happy computing.


Posted by Mark W. on December 20, 2001 9:29 AM

Have you used Sharing... Track Changes? ...

It's one of the better enhancements made to Excel
of late.

Posted by Damon Ostrander on December 20, 2001 12:23 PM

Re: Have you used Sharing... Track Changes? ...

Hi Mark,

Thanks for the reminder. I had forgotten about the change tracking capability. I use it all the time in Word, but had never used it in Excel. It's an excellent feature.