If cell L2 has Text in it return Date in J2

[Solved]
Report
-
Posts
1320
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
October 8, 2021
-
Hello,

Can anyone help with this?

I am designing a Task Tracker. The user wants a Date and Time to auotgenerate when Text is entered into a Cell. This timestamps the activity.

Appreciate you help

System Configuration: Windows / Edge 92.0.902.62

2 replies

Posts
1320
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
October 8, 2021
239
Hello The397Life,

You haven't given us much to work with but here's a starting point:-

This can be done with an event code as follows:-
Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Columns("L:L")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub

If Target.Value <> "" Then
Target.Offset(, -2) = Format(Now)
End If

End Sub


To implement this code:-
- Right click on the sheet tab.
- Select "View Code" from the menu that appears.
- In the big white code field that then appears, paste the above code.

Hence, each time an entry is made in a cell in Column L and the User then clicks away from the cell (or presses enter or down arrow), the corresponding cell in Column J will show the current date and time.

I hope that this helps.

Cheerio,
vcoolio.
1
Thank you

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

CCM 2821 users have said thank you to us this month

Firstly thank you for your help.

I put the code in the worksheet and it did display the date in the cell (Month/Date/Year). When clicked into the cell I could see 8/03/2021 6:42:18 PM. But it would not display this. Also I am in a country that puts the date Day/Month/Year. One questions:
1. How do I display Day/Month/Year and time?
Posts
1320
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
October 8, 2021
239
Hello The397Life,

As far as I can tell, you're in Australia so the standard format for Excel, based on local settings, is DD/MM/YYYY. You may want to check your machine's settings.
However, you can try this to see if it helps to correct the date:-

Change this line of code:-

Target.Offset(, -2) = Format(Now)


to

Target.Offset(, -2) = Now


Here's a link to a sample workbook showing how the minor change above works. Just make an entry in Column L:-

https://wetransfer.com/downloads/bac0241338e9b712062fe93b129fe7d820210803104019/1c1631

Cheerio,
vcoolio.