Static Date and Time code problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I'm working on a metrics log and I need to capture the current date and time
in the same cell and for it to remain static. I found a code on this website
but it only captures the time. I'm not familiar enough with this code to
manipulate it for both date and time. Can someone provide the code to capture
both date and time in the same cell?

Thanks in advance....it's greatly appreciated.

Laura
 
Hi, Laura-
Will you please post your code? If you're capturing the system time
and date and writing that value to a cell, it may be just a matter of
formatting the cell to show both date and time. For instance:

Dim Datestamp as Date
Datestamp = Now()
Range("A1").value = Datestamp

In this example, if cell A1 is formatted properly, it will show both
time and date.
 
Thanks Dave....

Here's the code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
If Target.Row = 1 Then Exit Sub
If IsEmpty(Target.Offset(0, 1)) Then
Target.Offset(0, 1) = Date And Time
Target.Offset(0, 1).NumberFormat = "m/dd/yy h:mm AM/PM"
End If
End Sub

The cell is formated to date and time...here's what it looks like on the s/s:

1/00/00 12:00 AM

I've played with the code a bit...but as you can see from the example above,
it's now working.

Thanks
Laura
 
Meant to say...it' not working.

Laura said:
Thanks Dave....

Here's the code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
If Target.Row = 1 Then Exit Sub
If IsEmpty(Target.Offset(0, 1)) Then
Target.Offset(0, 1) = Date And Time
Target.Offset(0, 1).NumberFormat = "m/dd/yy h:mm AM/PM"
End If
End Sub

The cell is formated to date and time...here's what it looks like on the s/s:

1/00/00 12:00 AM

I've played with the code a bit...but as you can see from the example above,
it's now working.

Thanks
Laura
Click to expand...
 
Hi, Laura-
Sorry for the delayed response: endless meetings droning on.

Please try changing this line:
Target.Offset(0, 1) = Date And Time
....to this, and let me know how it works:
Target.Offset(0, 1).value = now()
 
Hi Dave,

No Problem...totally understand about meetings...

That didn't work...here's what the code looks like now:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
If Target.Row = 1 Then Exit Sub
If IsEmpty(Target.Offset(0, 1)) Then
Target.Offset(0, 1).Value = Now()
Target.Offset(0, 1) = Time
Target.Offset(0, 1).NumberFormat = "m/dd/yy h:mm AM/PM"
End If
End Sub

It provides the time, but not the date: 1/00/00 2:02 PM

Thanks...:)
 
What about commenting out the line "'Target.Offset(0, 1) = Time"???

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
If Target.Row = 1 Then Exit Sub
If IsEmpty(Target.Offset(0, 1)) Then
Target.Offset(0, 1).Value = Now()
'Target.Offset(0, 1) = Time
Target.Offset(0, 1).NumberFormat = "m/dd/yy h:mm AM/PM"
End If
End Sub
 
Laura-
pikapika13 is correct: this line
Target.Offset(0, 1).Value = Now()
does the required job, but the line that follows it
Target.Offset(0, 1) = Time
changes it to time only. You can remove that second line or comment it
out, and it should work for you. Please let us know!
 
I have another question....If I want to insert another static date and time
stamp (to illustrate the closer of an issue) a few columns over, how do I add
this code to the existing code I already have?

Thanks
Laura
 
Laura- The new code appears between the stars, below:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
If Target.Row = 1 Then Exit Sub
If IsEmpty(Target.Offset(0, 1)) Then
Target.Offset(0, 1).Value = Now()
Target.Offset(0, 1).NumberFormat = "m/dd/yy h:mm AM/PM"
'***** additional code starts here
Target.Offset(r, c).Value = Now()
Target.Offset(r, c).NumberFormat = "m/dd/yy h:mm AM/PM"
'*****
End If
End Sub

Note you'll need to change the "r" and "c" to numbers- they are the
number of rows (r) and columns (c) from the starting position. So to
add the timestamp to a cell 5 cells to the right on the same row, the
(r, c) numbers are 0 and 5.
 
Dave,

So that code populates the date and time at the same time I first enter data
into the first column. What if I wanted to have the second date and time
populated at a later time? I was thinking when I have entered data into
column 8 and the date/time to populate in column 9. Is that possible?

Thanks.
 
Yes, it is, but you'd need to specify a different range. The original
code says "if an entry is made to a column other than A and not in cell
1 then apply the time in the cell immediately to the right. You'd need
to amend that to do the other chore when information is entered to
column 8. Easily enough done, but we'd need to make changes to the
original code to accommodate.

Do you mind emailing me? I'll see your response sooner.
CYCLEZEN atsign YAHOO dot COM
 
Hi Dave,

I'm trying to do same thing. Want to update column C with date when B is
updated, likewise, to update column H when cell in G is updated.

I've tried following the previous codes and was okay with regards to
time/date formats, but could get set up when one column was updated.

Please can you help?

Deb
 
Back
Top