Lock-in TODAY date?

[pearce_dan]

Is there any way to automatically lock in the date after you pull it up with the TODAY function? Or is there another function that will do what I'm trying to do?

I want it to automatically fill in today's date, when a certain empty cell has a value put in, then freeze there.

Thanks in advance!

[Oaktree]

CTRL semicolon

[pearce_dan]

Sorry to be dense. At what point to I do CTRL semicolon? Will that do it automatically? I'm trying to figure out how to have it lock in automatically as soon as it pulls up the date.

[VoG]

If you type in CTRL + ; an unchanging date will be entered.

Alternatively type =TODAY()

(don't press Enter)

and press F9.

[pearce_dan]

What does F9 do in this situation?

Here is my formula: =IF(N5="","",TODAY())

This will pull up today's date if there is any value in N5. If N5 is blank, it will remain blank.

What I'm trying to figure out is how to make it freeze the date once somebody puts a value into N5 so that I never have to touch it.

PS, CTRL ; is a great little shortcut. Don't know why that one's never crossed my path before.

[DiscoPistol]

This is a tad ragged but should work.....

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
a = Date
If Range("n5").Value <> Blank Then
If Range("a1").Value = Blank Then
Range("a1").Value = a
Exit Sub
End If

If Range("a1").Value < a Then
Exit Sub
End If
Range("a1").Value = a
End If

End Sub

......I think :o

[pearce_dan]

Thanks Disco, I'll give it a shot.

[pearce_dan]

BTW, if anybody knows how to do it outside of VBA, it'd be real helpful since I'm still in the learning stages of VBA.

[gardnertoo]

There is a way, fraught with danger. If you enable circular references (Options\Calculation and check "Iteration") you can write formulas that self-reference. Enable CR's, clear the contents of cell N5, and enter the following formula in cell O5:

=IF(NOT(O5=0),O5,IF(ISBLANK(N5),0,TODAY()))

(I've assumed that column O is where you want the date.) Once this is in cell O5, copy it down as far as you'll need, plus a few rows.

1. The formula first looks to it's own cell to find out if there's a date there already; if so the formula puts that date back in the cell. (For this reason, if you need to extend this formula down to more rows later, you must copy it from a cell whose result is zero, not a date.)
2. Then the formula looks at cell N5; if it's NOT empty, the formula puts today's date in the cell.

You'll now have zeroes in those cells until you enter something in the corresponding cells in column N. (Anything: text, numbers, formulas, they all trigger the ISBLANK logic in column O.)

The danger is you are now unprotected from ACCIDENTAL circular references in other cells. If that's not a concern, the above should work.

[Oaktree]

Hi, Dan.

There is no way to conditionally enter a static date without using VBA.

A couple of follow-up questions:

(1) What should happen if someone *changes* N5? The date cell change to today's date (i.e. show the date of the last update)? Or, do you just want the date cell to show the *first* time N5 was updated?

(2) Also, what if someone deletes the value in the date cell? (or do you want to prevent that from happening)?