AufoFill Date in column

[z28n4x4]

I have an excel sheet that when someone enters data into column G the time and date of when they entered it goes into column H. This "time stamp" needs to not change when you close and reopen it like it would if you use the "Now" function. Please post your replies. Thanks for your help. Eagerly awaiting your responses!!!

 

[HalfAce]

Hello z28n4x4, welcome to the board.
You say you "have an excel sheet that when someone enters data into column G the
time and date of when they entered it goes into column H."
Is it that you already have it working - and I assume using a formula in column H like
=NOW()
and that you want the time/date in Col H to remain static?

If I'm right then here are a couple ways to address this.
1) You can copy/pastespecial column H as values (either manually or via VBA) or
2) You can use VBA in the worksheet change event to auto stamp the time & date as a
ststic value.

If I'm on the right track then post back with what you're using and how you'd like to
handle it.

 

[Vangaard]

Here is a sample of something that I did very similar to what you are asking using VBA.

Sub time_stamp()
Dim MyTime
Dim MyDate
Dim exc As String
Dim x As Integer

For x = 1 To 50 'finds first empty cell
If IsEmpty(Cells(x, 2)) Then
Cells(x, 2).Activate
Cells(x, 2).Value = Date
Exit For
End If
Next x
' Moving to column "F" to enter Time
ActiveCell.Offset(0, 4) = Time
ActiveCell.Offset(0, 2).Activate

 

[Smitty]

Welcome to the Board!

See if this does it for you:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range
         <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN>
        <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
        <SPAN style="color:#007F00">'   Set Target Range</SPAN>
        Set rng = Range("G:G")
        <SPAN style="color:#007F00">'   Only look at that range</SPAN>
        <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
            <SPAN style="color:#007F00">'   Action if Condition(s) are met</SPAN>
            Target.Offset(, 1) = Format(Now, "hh:mm am/pm mm/dd/yy")
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

The code goes in the worksheet specific module.

Hope that helps,

Smitty

(Heya Dan!)

 

[HalfAce]

Hi Smitty,
Thats exactly what I was thinking if the OP wanted to go that route.
Just wasn't sure which way they wanted to go. (Still not I guess.)

 

[z28n4x4]

I have the "Now" function working in the column but I want the date and time to stay as a "date stamp" on when the data was entered into column G.

So Half Ace I would like to use your option #2

Right now I have the "Now" function in column H but updates to the current time and date not when the data was entered.

Thanks for your help and I am not to verse on VBA or entering code into excel. I know how to cut and paste into a worksheet but I dont know how to write code. Sorry for my ignorance.

I am going to try pennysavers code and see if it works.

Thanks Pennysavers it works!!!!

What do I need to do to in the code pennysavers wrote to have the code work on other columns as well? Meaning instead of having it handle columns g & H have it handle columns D & F?

Thanks a bunch guys!!!!

Looking forward to your reply to this question as well!!!!

 

[Smitty]

What do I need to do to in the code pennysavers wrote to have the code work on other columns as well? Meaning instead of having it handle columns g & H have it handle columns D & F?

Do you want it to work on D & G or just D instead of G? If the latter, just change Range("G:G") to Range("D:D"). The first option will take a bit more of a tweak, but it's easy to do.

Smitty

 

[z28n4x4]

D instead of G. Just want to know on how the coding works so I can attempt to fix future problems in the future. Thanks A LOT for your help!!!!!

Is there any good reading on coding that you could recommend?

 

[Smitty]

D instead of G

Just change Range("G:G") to Range("D:D").

Is there any good reading on coding that you could recommend?

There are several good books, Bill Jelen (aka Mr Excel) has one you can buy through the site. John Walkenbach is great too.

A Google search for "Free Excel VBA Tutorials" will turn up plenty and there's a good reference here: Microsoft Visual Basic Programmer's Guide.

Smitty