Static Date and Time Stamp Help VBA

Oxon258

New Member
Joined
Oct 29, 2016
Messages
9
I'm currently trying to add a date stamp (column A) and time stamp (Column B) after inputting data into column D for the first time (i.e. no updates in time or date after the first entry of a specific row in column D). The input, date and time should be on the same row.

I've hunted around and found code to do date only and time only, but with my current lack of skill I'm struggling to figure out how to merge the two. I'd very much appreciate your help on this, if possible! Below are the sections of cod I have so far:

PrivateSub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 And Target.Offset(0, -3).Value = "" Then
Target.Offset(0, -3) = Format(Now(), "DD/MM/YYYY")
EndIf
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 And Target.Offset(0, -2).Value = "" Then
Target.Offset(0, -2) = Format(Now(), "HH:MM:SS")
End If
End Sub
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Try this.
Code:
PrivateSub Worksheet_Change(ByVal Target As Range) 
    If Target.Column = 4 And Target.Offset(0, -2).Value = "" Then
        Target.Offset(0, -2).Value = Format(Now(), "HH:MM:SS") 
        Target.Offset(0, -3).Value = Format(Now(), "DD/MM/YYYY") 
    End If
End Sub
 
Upvote 0
Thanks for your help Norie. I'm quite a novice at this - it looks like it's working. The only thing is cells in column A and B populate even when i click in column D. Is there a way to control it so cells A and B only populate when text is entered?
 
Upvote 0
The code I posted should only be triggered when you enter a value in column D, did you change anything?

Do you have any other code?

PS I assume you fixed the typo PrivateSub.:)

By the way, this might be better as it checks both column A and B to see if they are empty before applying the date/time stamp.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 4 Then
        If Target.Offset(0, -2).Value = "" And Target.Offset(0, -3).Value = "" Then
            Application.EnableEvents = False
            Target.Offset(0, -2).Value = Format(Now(), "HH:MM:SS")
            Target.Offset(0, -3).Value = Format(Now(), "DD/MM/YYYY")
            Application.EnableEvents = True

        End If
    End If
    
End Sub
 
Last edited:
Upvote 0
I do have some other code in the sheet. I popped the new code in and it all works perfectly. That's ideal! Thanks very much. The more examples I see the more I'm getting to have a slight understanding of things. I am of course a reallllllllllllll long way off being competent at VBA!

Cheers again.
 
Upvote 0

Forum statistics

Threads
1,223,231
Messages
6,170,885
Members
452,364
Latest member
springate

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top