Question about dates.

[superbeast326]

I have two columns, A and B. Both have 50 rows. Column A is filled entirely with "No". What I want is that the moment I switch a row in Column A to "Yes". I want the row in Column B to store the date that the change was made. Is this possible?

 

[bensonsearch]

Hi,
You could do a formula that tests with an IF statement on A = NO. if = YES then insert date.

something like

=IF(A1="NO","",NOW())

make sure that B is set to date under the format cell options

 

[Hovrzone]

You could try this formula but just need to make sure Col B is date format.

=IF((A3)="YES","=TODAY()","")

 

[T. Valko]

I have two columns, A and B. Both have 50 rows. Column A is filled entirely with "No". What I want is that the moment I switch a row in Column A to "Yes". I want the row in Column B to store the date that the change was made. Is this possible?

See this:

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

 

[superbeast326]

Hi,
You could do a formula that tests with an IF statement on A = NO. if = YES then insert date.

something like

=IF(A1="NO","",NOW())

make sure that B is set to date under the format cell options

By doing so, won't column B all end up having the same date.

 

[bensonsearch]

only show the date from the time it says yes. the formulas should be there to start.

 

[superbeast326]

Column A is going to eventually be "Yes" all the way. So with that, will Column B be populated with different dates.

 

[superbeast326]

Because by using Now(), won't the value change everytime I open my Excel file?

 

[bensonsearch]

it shouldnt change, i wouldnt expect it to. you could get vba to do the task for you and just write the value in?

 

[Peter_SSs]

superbeast326

You are right that the formula suggested will not record "the date that the change was made" as you requested, it will simply show the current date for any rows where the change has been made at some time in the past.

The link provided in post #4 has some suggestions regarding this sort of thing. For your circumstances, you could try this. It assumes the values in column A are not the result of formulas.

You didn't say if it is possible, or what should happen if you subsequently change a "Yes" value back to "No" or to some other value? My code just leaves column B with the previous date in this circumstance.

To implement ..

1. Right click the sheet name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window and test.

<font face=Courier New><br><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)<br>    <SPAN style="color:#00007F">Dim</SPAN> Changed <SPAN style="color:#00007F">As</SPAN> Range, c <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <SPAN style="color:#00007F">Const</SPAN> myRange <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "A2:A51" <SPAN style="color:#007F00">'<- Change to suit</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> Changed = Intersect(Target, Range(myRange))<br>    <br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Changed <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Changed<br>            <SPAN style="color:#00007F">If</SPAN> UCase(c.Value) = "YES" <SPAN style="color:#00007F">Then</SPAN><br>                c.Offset(, 1).Value = <SPAN style="color:#00007F">Date</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> c<br>        Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>