if cell d2 has a value, todays date must apear in cell j2

[simeonmein]

Hi all
Basically I need a formula to show the current date in cell j2 that cell d2 got a value. The =today() formula donwork as the date must not change.
Please help

[alansidman]

this should do it for you

http://spreadsheets.about.com/od/tip...24add_date.htm

[simeonmein]

Good info, but not what I need, cell d2 gets its value from a userform, so I need a formula in cell j2 to add the date that d2 got its value. Thanx

[alansidman]

If you are getting your info from a userform, then some vba code to insert into your existing code.

Please Login or Register  to view this content.

[simeonmein]

Great! How do I make it apply to the whole column D and J?

[rollis13]

How about a formula like this in J2; you will need to activate Iteration / max = 1 in your Excel Options:

=IF(ISBLANK(D2),"",IF(J2="",TODAY(),J2))

[alansidman]

@rollis:

Using Today() will change the date each time the sheet is opened on a different day. The OP is looking for a solution that fixes the date.

[xwarlock10x]

=IF(D2<>"",IF(J2="",NOW(),J2),"")

THIS IS A LOOP FORMULA SO YOU MUST GO TO EXCEL OPTIONS/ FORMULAS.. UNDER "CALCLULATIONS OPTIONS" CHECK MARK "ENABLE ITERATIVE CALCULATION" THEN CHANGE THE "MAXIMUM ITERATIVE" TO 1
what this will do is if cell D2 is blank.. nothing but the second you put anything in D2.. it will time stamp cell J2 and wont change

[alansidman]

try this:

Please Login or Register  to view this content.

[rollis13]

@alansidman
Did you test my formula ? have a try with NOW (format cell hh:mm) instead of TODAY so you won't have to wait a day and see if it changes.
This is what IF(J2="",TODAY(),J2) is for.

@xwarlock10x
Thanks, didn't know how to translate the headings of the Option menus, I don't use english Excel.

[alansidman]

rollis13: Yes. If I used your formula, then I get the today's date 10/3/2013. I then close the file and change the system date in my machine to 10/4/2013 and then open the file. All the dates in column J are now 10/4/2013. I believe that the OP wants those dates to stay constant at 10/3/2013.

Alan

[simeonmein]

Hi all and thanx for the help. I tried both the formula and the code but to no success, nothing hapens when I enter the code, with the formula I get a circular error.
Please advise me as to where I must enter the code? Currently its in the sheets code, but nothing is happening.

[rollis13]

@alansidman
Works fine with my Excel 2003. The timestamp will not change even if you later update/change the contents of cell D2, it can be then changed only if you first delete D2.

@simeonmein
As said in posts #6 and #8 you need to activate the Iterative Calculation to avoid Circular error.

[alansidman]

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

[simeonmein]

@rollis13: I changed the calculation settings for formulas in options to 1, after this I got the circular error with no date apearing in cell j2. Can one not use a hlookup function?

@alansidman: tried it and no success.

[alansidman]

See attached file. Did you remember to save as a macro enabled file. .xlsm

[rollis13]

Please attach a sample of your sheet elsewise we will get into a never ending loop ;-)