Need help making X become today's date when entered

[Tayhan]

I have an intern going through a spreadsheet and updating process cards. Each process card is ordered by Customer part# and Warehouse#. Right now the intern puts an X in the column that she updates. What i would like to happen is, when the intern enters in x, excel automatically changes that to the date she entered said x. So for example, if today is 6/1/2015 and i put an X in column A, excel will change the X to 6/1/2015. The next problem is that this will be an ongoing process throughout the summer. If she enters an x on one day (6/1/2015) and then again on the following day i don't want her previous entries to change.

I meddled around with IF statements but realized the formula applies to the cell i have highlighted.

Ultimately i would like to apply this change to a range of cells. Is this even possible?

I hope my questions are clear enough to understand.

 

[bbott]

Welcome to the board.

You won't be able to do that with a formula. If you want to update a cell with the current date and keep that date, you would need to use VBA.

Or, rather than entering an X, you could just use CTRL+; to enter the current date in a cell.

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter an "x" in column A.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    If Target = "x" Then Target = Date
End Sub

 

[Tayhan]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter an "x" in column A.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    If Target = "x" Then Target = Date
End Sub

AWESOME! that worked perfectly. I will check back tomorrow and let you know if this worked exactly the way i want it to. Thank you!

 

[Tayhan]

Welcome to the board.

You won't be able to do that with a formula. If you want to update a cell with the current date and keep that date, you would need to use VBA.

Or, rather than entering an X, you could just use CTRL+; to enter the current date in a cell.

Thank you for the warm welcome. I believe the comment below is what you are referring to as a VBA? i tried what mumps has said and it works. i imagine this will also work for the days to come without erasing previous dates. Thank you for your response