Find out date filling cells
Solved/Closed
Related:
- Find out date filling cells
- If cell contains date then return value ✓ - Excel Forum
- Excel formula to check if cell contains a date - Excel Forum
- Excel arrow keys not moving cells - Guide
- Based on the cell values in cells b77 - Excel Forum
- Which example represents cell data with the date format applied to it? - Excel Forum
1 response
David Webb
Posts
3177
Registration date
Monday November 25, 2019
Status
Administrator
Last seen
May 15, 2023
6,926
Dec 4, 2019 at 02:36 AM
Dec 4, 2019 at 02:36 AM
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
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
Dec 4, 2019 at 05:47 AM
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
Dec 6, 2019 at 05:32 AM
But do you have any other suggestion ?
Thanks again
Marcus
Dec 6, 2019 at 06:04 AM
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
Dec 6, 2019 at 11:16 AM