Find out date filling cells [Solved]

Report
-
 LamoIdiot5 -
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
371
Registration date
Monday November 25, 2019
Status
Administrator
Last seen
January 10, 2020
327
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
1
Thank you

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 5641 users have said thank you to us this month

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
David Webb
Posts
371
Registration date
Monday November 25, 2019
Status
Administrator
Last seen
January 10, 2020
327
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
Give a man a fish he eats today, teach a man how to fish he eats for a lifetime. Hopefully Marcus has learned something.