Subscribe now and choose from over 30 free gifts worth up to £49 - Plus get £25 to spend in our shop
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?
Using circular references and worksheet functionsYou 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.