Time stamp Data entry in Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-2023473%22%20slang%3D%22en-US%22%3ETime%20stamp%20Data%20entry%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2023473%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20record%20date%20and%20time%20in%20a%20cell%20whenever%20data%20is%20entered%20into%20a%20worksheet.%20I%20hav%20tried%20%22%2B%40LastModified()%22%20but%20it%20is%20not%20doing%20what%20I%20want.%20Any%20suggestions%20%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2023473%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2023582%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20stamp%20Data%20entry%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2023582%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F914656%22%20target%3D%22_blank%22%3E%40AHKOVC%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20need%20VBA%20for%20this.%20A%20formula%20cannot%20do%20what%20you%20want.%3C%2FP%3E%0A%3CP%3ERight-click%20the%20sheet%20tab.%3C%2FP%3E%0A%3CP%3ESelect%20'View%20Code'%20from%20the%20context%20menu.%3C%2FP%3E%0A%3CP%3ECopy%20the%20following%20code%20into%20the%20worksheet%20module%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%0A%20%20%20%20Application.EnableEvents%20%3D%20False%0A%20%20%20%20'%20Change%20C1%20to%20the%20cell%20in%20which%20you%20want%20the%20time%20stamp%0A%20%20%20%20Range(%22C1%22).Value%20%3D%20Now%0A%20%20%20%20Application.EnableEvents%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESwitch%20back%20to%20Excel.%3C%2FP%3E%0A%3CP%3ESave%20the%20workbook%20as%20a%20macro-enabled%20workbook%20(.xlsm).%3C%2FP%3E%0A%3CP%3EMake%20sure%20that%20you%20allow%20macros%20when%20you%20open%20the%20workbook.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I am trying to record date and time in a cell whenever data is entered into a worksheet. I hav tried "+@LastModified()" but it is not doing what I want. Any suggestions ?

 

1 Reply

@AHKOVC 

You need VBA for this. A formula cannot do what you want.

Right-click the sheet tab.

Select 'View Code' from the context menu.

Copy the following code into the worksheet module:

 

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    ' Change C1 to the cell in which you want the time stamp
    Range("C1").Value = Now
    Application.EnableEvents = True
End Sub

 

Switch back to Excel.

Save the workbook as a macro-enabled workbook (.xlsm).

Make sure that you allow macros when you open the workbook.