# Macro needed to log date and time

#### Savuti

##### Board Regular
Hello, the code below is not complete. I need to have the date and time show up in columns G and H respectively when the word Sunday shows up in the corresponding B column. I need the same for J and H if the word Monday shows up in the corresponding B column.

Thanks for the help

1st -
Code:
``````Cells(Target.Row, "G").Value = Date
Cells(Target.Row, "H").Value = Time``````

2nd -
Code:
``````Cells(Target.Row, "J").Value = Date
Cells(Target.Row, "K").Value = Time``````

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### pedie

##### Well-known Member
You look pretty comfortable with your code but try this too...

Code:
``````[/FONT]
[FONT=Courier New]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim lr As Long
lr = Range("B" & Rows.Count).End(xlUp).Row[/FONT]
[FONT=Courier New]If Target.Count > 1 Then Exit Sub[/FONT]
[FONT=Courier New]For Each Target In Range("B1:B" & lr)
If Target.Value = "Sunday" Then Cells(Target.Row, "G").Value = Now
If Target.Value = "Monday" Then Cells(Target.Row, "J").Value = Now
Next Target
End Sub``````

#### Savuti

##### Board Regular
pedie, this does work, but how would I get the date and time to show up in cell G and H respectively if Sunday is in "B" and the date and time to show in J and K if Monday is in "B"

I hope this makes sense and I thank you for your time.

#### pedie

##### Well-known Member
Try

Rich (BB code):
``````private sub worksheet_selectionchange(byval target as range)
dim lr as long
lr = range("b" & rows.count).end(xlup).row
if target.count > 1 then exit sub
for each target in range("b1:b" & lr)
if target.value = "sunday" then cells(target.row, "g").value = date and cells(target.row, "h").value = time
if target.value = "monday" then cells(target.row, "j").value = date and cells(target.row, "k").value = time
next target
end sub``````

#### Savuti

##### Board Regular
pedie, strangely the time does not show up and the date shows as 00/01/1900

#### pedie

##### Well-known Member
This works for me. Try checking the format part too.
else we can use format(range,"yourformat")..

Code:
``````[/FONT]
[FONT=Courier New]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim lr As Long
lr = Range("B" & Rows.Count).End(xlUp).Row
If Target.Count > 1 Then Exit Sub
For Each Target In Range("B1:B" & lr)
If Target.Value = "Sunday" Then
Cells(Target.Row, "G").Value = Date
Cells(Target.Row, "H").Value = Time
ElseIf Target.Value = "Monday" Then
Cells(Target.Row, "J").Value = Date
Cells(Target.Row, "K").Value = Time
Else
End If
Next Target
End Sub``````

#### Savuti

##### Board Regular
pedie, everything goes to the right place - date and time, every time though I make an entry down the rows in B column, all the previously entered times in H and K update to the time of the most recent entry. I am wondering if there is something else in my spreadsheet that could be out of wack that is causing this. You have been most gracious and patient, perhaps it is something that you are aware of and have a fix, otherwise I will leave the time stamp out and just use the date.
Thanks
once again.

#### pedie

##### Well-known Member
Will check this again later, maybe someone else too can look into this..
got some work at the moment

Replies
3
Views
206
Replies
5
Views
83
Replies
8
Views
171
Replies
2
Views
70
Replies
5
Views
89

1,172,044
Messages
5,878,875
Members
433,380
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back