Cell that takes value of creation date

**Bramjonker** · 07-04-2012, 09:37 AM

Hi everybody,

Hope someone can help me with a smart solution for my problem:

Each of our customers has an unique customer #. This number is a following number on the last used customer number and the date of creation of the number. For the creation date we cannot use the =today() formula as this alters due time. is there a formula for the following;

When someting is entered in cell B2 can a formula place in B1 a date of creation. So this is the date when something is placed in B2 (and not change due to time)?

Hope someone can help me! Thanks in advance

**JBeaucaire** · 07-04-2012, 10:53 AM

No, you can't really use a FORMULA for this, formulas recalculate, so the date would change. You need a non-formula method of applying a date to that cell.

Data Validation
1) Find an empty cell unseen on your sheet and enter the formula =TODAY() into that cell.

2) With that cell still selected, type MyDate into the Name Box (just left of the formula bar). You have now named that cell and can use it in as Data Validation list source.

3) Click on B1 and apply these Data > Data Validation settings:
Allow: List
Source: =IF(B2<>"", MyDate)

Now, when a value is placed in B2, you will be able to click on B1 and the date appears in a drop down, easy to click on.

You can apply conditional formatting to B1, too, so that if B2 has a value and B1 does not, B1 lights up in red to draw the users attention.

=========================
VBA - macros

Not everyone wants macros enabled on their workbooks, so I won't go into detail other than to say a macro could be installed in the worksheet to watch cell B2 and anytime a change is made manually to B2, a Datestamp is physically added to B1.