Time-stamping existing data with date created

[coco5280]

Hello!

I have stumbled on this community and man, do I have a problem that I can not solve by myself! I have a spreadsheet in which I have data in columns A-F that I add to regularly. I recently wanted to update it so that I have a timestamp of when each cell in column A was entered. The issue that I am running into is that I have 150+ rows in which I do not know when the data was added. I have been messing around in VBA to figure out how to do this and from researching online, it seems that it might be impossible. BUT, I found this community and felt that it might be worth a shot to ask since the other articles were from 5+ years ago and there seems to be people who are far more experienced than me.

I want to be able to populate a timestamp in every row of column G that gives the date in which the data in column A of the corresponding row was added (NOT modified). I want this to be a date that does not change if there is ever any modification to the cell.

Please let me know if it is possible to generate this for existing data! Any help is greatly appreciated.

Cheers!

 

[rollis13]

Have a try with these macros to be placed in the sheet's vbe module; if I correctly understood your request they should do the job. Feel free to adjust whatever doesn't apply .

Option Explicit
Dim CellValue  As Variant

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub       'exit if multicell selection    
    CellValue = Target.Value                      'detect original value
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub       'exit if multicell selection
    If Target.Column <> 1 Then Exit Sub           'exit if not column A
    If Target.Value = "" And CellValue = "" Then Exit Sub 'exit if I cancel an empty cell
    Application.EnableEvents = False
    If CellValue = "" Then Target.Offset(0, 6).Value = Now() 'if activecell was empty add time-stamp in columnn G
    CellValue = vbNullString                      'reset variable to detect cancel/change of same cell
    Application.EnableEvents = True
End Sub

 

[Dermot]

If you're asking about existing data, no, there is no way to know when it was entered

 

[coco5280]

Have a try with these macros to be placed in the sheet's vbe module; if I correctly understood your request they should do the job. Feel free to adjust whatever doesn't apply .

Option Explicit
Dim CellValue  As Variant

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub       'exit if multicell selection   
    CellValue = Target.Value                      'detect original value
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub       'exit if multicell selection
    If Target.Column <> 1 Then Exit Sub           'exit if not column A
    If Target.Value = "" And CellValue = "" Then Exit Sub 'exit if I cancel an empty cell
    Application.EnableEvents = False
    If CellValue = "" Then Target.Offset(0, 6).Value = Now() 'if activecell was empty add time-stamp in columnn G
    CellValue = vbNullString                      'reset variable to detect cancel/change of same cell
    Application.EnableEvents = True
End Sub

That works for me!! It appears that others are not able to generate the date when they enter something into column A. Is there a way to have this work for all users and not just me?

 

[rollis13]

There are no limitations for different users.
If you are managing the file as a shared file then there could be limitations on multi-access.
Are you sure that other user have activated the use of macros in their Excel ?

 

[coco5280]

There are no limitations for different users.
If you are managing the file as a shared file then there could be limitations on multi-access.
Are you sure that other user have activated the use of macros in their Excel ?

I am sharing the file. I will check with them that they are allowing macros