I would abandon the use of a formula which uses the function TODAY().

TODAY() is a volatile function so will not remain static.

To get a static date entered you can use VBA sheet event code.

Right-click on the INPUT sheet tab and "View Code"

Copy/paste the following code into that module.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 2 Then
n = Target.Row
If Me.Range("B" & n).Value < "" Then
Me.Range("A" & n).Value = Date
End If
End If
enditall:
Application.EnableEvents = True
End Sub

If you do want to use the volatile TODAY() function, see John McGimpsey's site
for
a method using circular references.

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

Note also the VBA solution, which is similar to above event code.


Gord Dibben MS Excel MVP

On Mon, 9 May 2011 21:51:26 +0100, Colin Hayes wrote:


Hi all

I have a small problem that I hope someone can help with.

In cell A1 I have this code :

=IF(B1=""," ",TODAY())

With this , if B4 is empty the cell is empty. If B4 has content then it
puts the date.

I replicate this down to A19. As I make entries in the cells in column B
, so the date of entry is recorded.

Unfortunately of course the date of entry is updated to the present date
when I open the workbook. How can I fix the date of entry so that it
remains at the date entry was originally made in column B?

Grateful for any assistance.