Excel help, date stamping something

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?

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?

http://www.mcgimpsey.com/excel/timestamp.html

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.

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