+ Reply to Thread
Results 1 to 3 of 3

Cell that takes value of creation date

  1. #1
    Registered User
    Join Date
    04-23-2012
    Location
    Amsterdam, Netherlands
    MS-Off Ver
    Excel 2007
    Posts
    24

    Cell that takes value of creation date

    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

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Cell that takes value of creation date

    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.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Cell that takes value of creation date

    Thanks for the feedback, Bram.

    If that takes care of your need, please select Thread Tools from menu above and set this topic to SOLVED.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1