Microsoft Excel - How to capture date in next collumn?

Asked By Saber on 14-Dec-12 08:37 PM
Hi All,

If I enter any data in C12 what ever changes I made whenever I made next collumn C13 will capture the date when it was changed. Is this possibel can we do this?

Harry Boughen replied to Saber on 14-Dec-12 11:38 PM
Hello Saber,
You say you want the result in the next column but you give an address for the row below.
If you put the following code in for the appropriate worksheet it will put the date in the next column on the same row.

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

    If ActiveCell.Row > 1 Then
      If ActiveCell.Offset(-1, 0).Value <> 0 Then
        ActiveCell.Offset(-1, 1).Value = Date
      End If
    End If

End Sub

If you want the result somewhere else you just have to change the offset values. Or if you want it to work for a specific cell then you need to specify that cell as a range.
HTH
Harry
John D replied to Saber on 15-Dec-12 09:45 AM
Hi
Your request is C12 and C13 so this is specific to your demand.
Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Address <> Range("C12").Address Then Exit Sub
    If Target.Address = Range("C12").Address Then
         Range("C13").Value = Format(Date, "mmm-dd-yyyy")
      End If
End Sub

Saber replied to John D on 15-Dec-12 11:11 AM
Thank you for the response!!!!!!!!!!!!!!!


I need this data without using the macro. is it possible?

Harry Boughen replied to Saber on 15-Dec-12 02:20 PM
Hello Saber,
Not without converting the formulaic result to a value and if that is the case you may as well enter it manually in the first place.
Harry
Saber replied to Harry Boughen on 15-Dec-12 02:27 PM
Hey Harry,

Thank you for your response....Your really helpful as always...


My concern is I have different excel files and will be work by different people. I want to know on which date the last entry has made on the particular cell. 

The reason behind I don't want to use macro is if I use Macro then I have to change each and every sheet to .xlsm because I'm using excel 2010. It will be re work for me.

Please help me to get out from this issue.
Harry Boughen replied to Saber on 15-Dec-12 05:04 PM
Hello Saber,
Try this
=IF(B1<>”",IF(A1=”",TODAY(),A1),”")
where B1 is you data entry cell and A1 is your date cell. This only works for a new entry not a change.  You also have to turn on iteration (maximum iterations = 1 will suffice) to allow the circular reference to work.
Hope this helps.
Harry
Saber replied to Harry Boughen on 15-Dec-12 05:15 PM
Thank you very much Harry!!!!!!!!!!!!!!!your just awesome!!!!!!!!!!!!!!