if/then insert date but don't want automatic updates

[redleg]

I have a spreadsheet tracking personnel actions. I tried the following
formula =IF(L2="","",TODAY()) but I do not want the date stamp to
automatically update when the spreadsheet opens. How can I write a simple
formula so that when the cell has text entered into it, the date will be
recorded in the adjacent cell as a static stamp? Thanks.

 

[Jim Thomlinson]

Long story made short... what you are asking for requires macros. If you want
to persue that it is a fairly simlpe macro but it many people don't wnat to
go there...

 

[redleg]

I am willing to try if you have an example I can tinker with. Thanks.

 

[T. Valko]

See this:

http://www.mcgimpsey.com/excel/timestamp.html

 

[Gord Dibben]

As you have found out TODAY() is a volatile function that updates each day.

I would suggest some type of event code that adds a static timestamp rather
than have a formula-derived one.

But you can prevent a formula from updating using worksheet functions alone.

Couple of methods on John McGimpsey's site

One without VBA and one with.

http://www.mcgimpsey.com/excel/timestamp.html


Gord Dibben MS Excel MVP

 

[Jim Thomlinson]

Start by recording a macro. Don't care what it is. When that is done, Select
Tools -> Macro -> Macros -> Edit

Add the following code

Public Function StaticDate(ByVal Cell As Range) As Variant
If Cell.Count > 1 Then
StaticDate = -1
Else
StaticDate = Now()
End If
End Function

Now go back to your spread sheet. Add the formula
=StaticDate(A1)
to the cell where you ant the date to appear (change A1 to the cell that
triggers the date to appear).

Now whenever cell A1 is changed the date will be changed in the cell where
you added this formula. This is a regular formula that can be copied and
pasted and...

 

[Katlegal]

Jim,

This macro worked for me to insert the date; however, I want to use it for
multiple rows of data and ONLY if there is something in the adjacent cell.
As I am inserting this reference, it inserts today's date, even if there is
nothing in the adjacent cell.

How do I fix that??

 

[Gord Dibben]

=IF(A1="","",staticdate(A1))

If you don't want to use a formula you could use event code.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
If Target.Cells.Column = 1 Then
N = Target.Row
If Excel.Range("A" & N).Value <> "" Then
Excel.Range("B" & N).Value = Now
End If
End If
enditall:
End Sub

Select your sheet tab and "View Code". Copy/paste the code into that sheet
module.

Edit to suit then hit Alt + q to return to Excel.

As written the code will place a staic date/time in column B when data is
entered in column A.


Gord Dibben MS Excel MVP