auto input of dates IF/THEN

[FRAC]

Hi,

I'm trying to make a formula which makes a date pop up, if a cell has been used..
Example: if text is entered in cell A5, then todays date should show in cell C5. But if there is no text in A5, then cell C5 should appear empty.

Is this possible?
I'm using Excel 2000.

Brgds André

 

[FRAC]

To make the date static, you'll need some code. do a search of the board for time stamp. There are lots of code on this subject.

Thanks a lot! I'll try that!

Brgds André

 

[Smitty]

Brian asked me to pitch in on this one. Following is some worksheet change code; whenever something is entered in column A, column C in the same row will have the static date added. Conversly, if a cell in column A is cleared, the same row in column C will be cleared. Comment out the last row if you don't want the entry cleared (put an apostrophe at the beginning of the line to comment it).

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range
    <SPAN style="color:#007F00">' Only look at single cell changes</SPAN>
        <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
        <SPAN style="color:#00007F">Set</SPAN> rng = Range("A:A")
        <SPAN style="color:#007F00">' Only look at that range</SPAN>
        <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
            <SPAN style="color:#007F00">'   Add the Date in Column C whenever an entry is made in the same row in Column A</SPAN>
            <SPAN style="color:#00007F">If</SPAN> Target <> "" <SPAN style="color:#00007F">Then</SPAN> Target.Offset(, 2) = <SPAN style="color:#00007F">Date</SPAN>
            <SPAN style="color:#007F00">'   Clear the cell in Column C if the same row in Column A is cleared</SPAN>
            <SPAN style="color:#00007F">If</SPAN> Target = "" <SPAN style="color:#00007F">Then</SPAN> Target.Offset(, 2) = ""
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

To use it, right click the sheet tab and select View Code, then paste the above code in the module window that opens on the right. That's the worksheet module and is where worksheet level events like this one need to go.

Hope that helps,

Smitty

 

[FRAC]

Perfect!
Just what I wanted!

Thanks a lot!!


Brgds André

 

[howzat]

BTW, your original formula =IF(A5="","",TODAY()) would have presented you with an error message as your excel is set up to use colons ( ; ) as a formula separator, as apposed to commas (,)

=IF(A5="";"";TODAY()) would work, but not for your purposes as it is not static (as above)

 

[ADAM12]

**** you guys are good!!