Help with Date and Time Return

[ESIPPEL]

I am creating a Call Sheet to record Times, Dates, and Notes that the listed parties were contacted.

The end goal is to be able to enter in a cell the notes from the call, then an adjacent cell will read time that the notes were entered.

Is there a way to get the return of when a cell was modified (Date, Time) or even a just date of the modification?

I know about the =NOW() formula, but how could I make sure it only gives the Date/Time of the cell's modification and not just the update every time the workbook is opened?

Any help is appreciated.
Thanks, E

 

[Penmark71]

Hi E,

This is my first response on this site, so hopefully this is OK...

What I did is record a little macro for this. For my example, I used column C for my notes, and the column header was cell C1. I started recording the macro with Relative Referenced off to get to C1, then turn Relative References ON. Then I hit Ctrl + Down Arrow to get to the last entry of the column. Then went to the cell to the right to enter my =NOW() formula. Then I copied that cell and used paste special in the first two columns (one for date and one for time). Before I stopped recording, I deleted the =NOW() formula. Then I stopped recording. All you have to do next is format each of the cells the way you want it to display for Time and Date.

Sub NowOne()
'
' NowOne Macro
'
' Keyboard Shortcut: Ctrl+Shift+A
'
Range("C1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=NOW()"
ActiveCell.Select
Selection.Copy
ActiveCell.Offset(0, -3).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(0, 2).Range("A1").Select
Application.CutCopyMode = False
Selection.ClearContents
End Sub

 

[ESIPPEL]

Thanks for the help! Will this update the date/time modified any time the cell is changed after the original edits?

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 Then
    Application.EnableEvents = False
    Target.Offset(, 1).Value = Now
    Application.EnableEvents = True
End If
End Sub

Entering a value in column C will cause the date and time to be written to column D as a static value.

 

[ESIPPEL]

Thank you both!! I modified your codes and got it working brilliantly!
ES