EXCEL DATE/TIMESTAMP

[PaulDJ]

HOW DO I DATE STAMP/TIME STAMP A INPUT FIELD BUT MAKE SURE IT DOESNT CHANGE WHEN THE NEXT ENTRY BELOW IS ENTERED EG:
A1 IS THE DATA INPUT FIELD B1 IS THE FORMULA FOR DATE AND TIME WHICH I COPY DOWN THE COLUMN
A1 DATA ENTERED B1 GIVES ME DATE AND TIME BUT WHEN A2 IS ENTERED THE TIME CHANGES NOT ONLY IN B2 BUT IN B1 TOO. ITS A BIT OF A BUGGER
MY B COLUM FORMULA IS AS FOLLOWS:
=IF(A3=",",IF(B3="",NOW(),B3),NOW())

 

[Smitty]

Welcome to the Board!

You don't with a formula which will keep calculating. You need VBA. Do a Board Search for "Time Stamp"...There are plenty of relevant discussions that include the necessary code.

HTH,

Smitty

P.S. Your Caps Lock is stuck.

 

[PaulDJ]

Oops Thanx
Did a date stamp search came up with nix and thanx for the welcome!!

 

[Smitty]

"Nix"... Does that mean you didn't find anything?

Here's code that will put the date in coloumn B for an entry in the corresponding column in A:

<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("A:A")
        <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) = <SPAN style="color:#00007F">Date</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

HTH,

Smitty