A Date Stamp function that executes itself?

  • Thread starter Thread starter firewound
  • Start date Start date
F

firewound

Here's what I'm trying to achieve:

When I enter a number (0 or 1) into a previously blank cell, I want th
date and time when that entry was made captured automatically by th
cell next to it.

I've tried using an =if(isblank(A1),"",now())
But we all know that auto-updates everytime new data is inputted. Wha
I need is a static date.

It's impractical to use the Ctrl + ; macros every single time.

Any ideas would be really appreciated
 
Hi,

As I'm just learning to manipulate macros myself, this may
not be feasible but would a macro, executed by input into
the blank cell work. The macro could copy the cell where
the Now() function was and paste special/value into the
cell of your choosing.

Might work. Now, if I can locate how to make a macro auto-
execute, I think I'll try it.....:)

Don
 
Hi ....
You can place Date, Time, or Now into cell in column A
if another cell on same row is changed. Now is date + time.

Event Macros, Worksheet Events and Workbook Events
http://www.mvps.org/dmcritchie/excel/event.htm#datetimestamp
Directions are there to install but it is simply
Right click on sheet tab, View code, insert the following
making changes as you like, You might want to fill in
Column B cell instead of Column A cell.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not IsEmpty(Cells(Target.Row, "A")) Then Exit Sub
If Not IsEmpty(Target.Value) Then Cells(Target.Row, "A") = Now
End Sub
 
Hi Dave,

Works great and I think I even understand it. Even locks
the date and time stamp for him. But is there a way to
limit the "input" cell to one cell?...say in this case if
C3 is the trigger cell. Other data may be inserted in
Row3 prior to C3 having an entry.

Thanks for the code.

Don
 
Hi Don,
There are probably some examples on the same page
but it may take awhile for them to "stand out".

To limit to one cell
if target.address <> "$B$2" then exit sub
which of course is the same as
if target.address(0,0) <> "B2" then exit sub

to limit to one column i.e. column B
if target.column <> 2 then exit sub
 
Hi Dave,

Thanks again and I'll take a close look at the page
again. Although I wasn't the original poster here, you've
helped me a great deal in understanding this function.

Thanks again....you'll be hearing from me again.

Don

-----Original Message-----
Hi Don,
There are probably some examples on the same page
but it may take awhile for them to "stand out".

To limit to one cell
if target.address <> "$B$2" then exit sub
which of course is the same as
if target.address(0,0) <> "B2" then exit sub

to limit to one column i.e. column B
if target.column <> 2 then exit sub
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]

Works great and I think I even understand it. Even locks
the date and time stamp for him. But is there a way to
limit the "input" cell to one cell?...say in this case if
C3 is the trigger cell. Other data may be inserted in
Row3 prior to C3 having an entry. -----
Click to expand...
http://www.mvps.org/dmcritchie/excel/event.htm#datetimestam
p

.
Click to expand...
 
This formula is long but works well for me. Keep in mind that when
make the I was like 2 days old in Excel.

=IF(B2=0,"",IF(M2="",NOW(),M2))

hope this help
 
This formula is long but works well for me. Keep in mind that when
make the I was like 2 days old in Excel.

=IF(B2=0,"",IF(M2="",NOW(),M2))

hope this helps

by the way my B will be your A and my M will be your
 
This formula seems to create a circular reference, and it sometime
results in a 1900 0:00 value for the now() function
 
Back
Top