AufoFill Date in column

z28n4x4

New Member
Joined
Oct 2, 2006
Messages
6
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!!!
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
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

New Member
Joined
Oct 19, 2005
Messages
35
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

Legend
Joined
May 15, 2003
Messages
29,536
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454

ADVERTISEMENT

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.) :biggrin:
 

z28n4x4

New Member
Joined
Oct 2, 2006
Messages
6
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!!!!

:LOL:
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536

ADVERTISEMENT

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

New Member
Joined
Oct 2, 2006
Messages
6
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

Legend
Joined
May 15, 2003
Messages
29,536
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
 

Forum statistics

Threads
1,136,712
Messages
5,677,335
Members
419,688
Latest member
sarahmichelle

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
Top