Lock-in TODAY date?

pearce_dan

New Member
Joined
Jan 13, 2006
Messages
36
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!
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

pearce_dan

New Member
Joined
Jan 13, 2006
Messages
36
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

Legend
Joined
Jun 19, 2002
Messages
63,650
If you type in CTRL + ; an unchanging date will be entered.

Alternatively type =TODAY()

(don't press Enter)

and press F9.
 

pearce_dan

New Member
Joined
Jan 13, 2006
Messages
36

ADVERTISEMENT

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

Active Member
Joined
Jun 6, 2006
Messages
261
This is a tad ragged but should work.....

Code:
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 :eek:
 

pearce_dan

New Member
Joined
Jan 13, 2006
Messages
36

ADVERTISEMENT

Thanks Disco, I'll give it a shot.
 

pearce_dan

New Member
Joined
Jan 13, 2006
Messages
36
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

Well-known Member
Joined
Jul 24, 2007
Messages
938
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:
Code:
=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

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,053
Office Version
  1. 365
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)?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,203
Messages
5,857,902
Members
431,906
Latest member
Reinette

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top