If cell L2 has Text in it return Date in J2
Solved/Closed
The397Life
-
Aug 2, 2021 at 08:38 PM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Aug 3, 2021 at 06:44 AM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Aug 3, 2021 at 06:44 AM
Related:
- If cell L2 has Text in it return Date in J2
- If cell contains date then return value - Excel Forum
- If cell A1 has text then cell B2 has today's Date ✓ - Excel Forum
- Wow equipment return ✓ - Network Forum
- If cell contains (multiple text criteria) then return (corresponding text criteria) ✓ - Excel Forum
- Conditional formatting if cell contains text - Excel Forum
2 responses
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Updated on Aug 3, 2021 at 03:49 AM
Updated on Aug 3, 2021 at 03:49 AM
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:-
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.
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.
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Aug 3, 2021 at 06:44 AM
Aug 3, 2021 at 06:44 AM
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:-
to
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.
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.
Aug 3, 2021 at 04:49 AM
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?