Viewing 5 posts - 1 through 5 (of 5 total)
  • Excel help, date stamping something
  • mrmo
    Free Member

    Looking at adding a date field to a document. having a little problem.

    The idea is to use the date as part of a serial number, first part is user, second part is date and third part is a number.

    The user is easy i can pull that from the machine, third part is user defined.

    Problem is the date, all the functions i can think of would keep changing the date which is not what is wanted. The first time the finished document is saved, that is the date i want. As this is a template the creation date is irrelevant, a modification date would change if someone opened the file, so is there another way?

    Stoner
    Free Member

    I suppose you could write some “self destroying” code so that it only ever runs once.

    Or maybe the code wont run if the time stamp cell has content?

    Fresh Goods Friday 696: The Middling Edition

    Fresh Goods Friday 696: The Middlin...
    Latest Singletrack Videos
    miketually
    Free Member

    Using circular references and worksheet functions

    You can use a circular reference to enter the time when a change is made in another cell, then maintain that time. Choose Tools/Options/Calculation (Preferences/Calculation for Macs) and check the Iteration checkbox. Then, if your target cell is A1 and you want the date/time to appear in B1, enter this in B1:

    =IF(A1=””,””,IF(B1=””,NOW(),B1))
    Format B1 as you wish to display date, time, or both. If A1 is initially blank, B1 will return a null string (“”). When a value is entered into A1, B1 will evaluate as “”, therefore NOW() will be returned. After that (as long as A1 remains populated), B1 will evaluate to a date/time and therefore will return the value in B1 – i.e., the date/time.

    That’s simple and clever. Nice.

    mrmo
    Free Member

    i like that, does exactly what i think i need.

Viewing 5 posts - 1 through 5 (of 5 total)

The topic ‘Excel help, date stamping something’ is closed to new replies.