Find out date filling cells [Solved]

Report
-
 Marcus -
Hi,
First of all thanks for your help
I have a big excel file which I have been filling it for the last 2 years.
There is a way to find out the date of every time I have been introducing data?
I mean to make a column with the date of every updated cell.

Thanks again

Marcus

1 reply

Posts
1536
Registration date
Monday November 25, 2019
Status
Administrator
Last seen
October 20, 2020
2,386
Hello Marcus,

After looking into this, it would appear that in Excel this feature does not exist. However you can do it with Excel VBA function.

1. In your Excel worksheet, cllick on ALT + F11 to open the Visual Basic editor
2. In Visual Basic Editor, click on the “Insert” menu
3. Select “Module”
4. Paste the following code into the new module:Public Function ModDate()ModDate =
Format(FileDateTime(ThisWorkbook.FullName), “m/d/yy h:n ampm”)End Function

5. Before saving your module, make sure to save your Excel file as Excel Macro-Enabled Workbook
6. Save your module
7. Return to your Excel worksheet
8. Paste the following code into the cell where you want to display the last modification time: =ModDate()

Let me know if this helps,
David
Hi David,
Thanks so much for your reply.
When doing as you said I got the message
"Compile error:

Expected: end of statement"


Maybe you should know I work with Imac
Thanks again and all the best

Marcus
Sorry David,
But do you have any other suggestion ?

Thanks again
Marcus
Posts
1536
Registration date
Monday November 25, 2019
Status
Administrator
Last seen
October 20, 2020
2,386
Hello Marcus, I've looked across the French forum for CCM and another possible solution...

Private Sub Worksheet_Change(ByVal Target As Range) Dim col As byte If Intersect(Target, Range("A1:D5")) Is Nothing Or Target.Count > 1 Then: Exit Sub col = Target.column Cells(6, col) = Target.Address & " Modified the " & Format(Date, "dd/mm/yy") End Sub

If not, have a look here, it's not CCM, but it may help. This is the code they use below:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("A1:C3"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 3).ClearContents
Else
With .Offset(0, 3)
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
End Sub

Besides that, I'm sorry I can't be of more help,
David
thanks a lot David