[Solved] Create Timestamp in Excel

[Bradbu]

Hi
I have a spreadsheet and I want to create a timestamp when the status is Open and a seperate timestamp when the status is closed. The timestamp I have created works except when the status moves from Open to closed the Timestamp that was created when it was first opened disappears and I need it to remain in place. Feel this can be achieved either by updating my formula or by adding a seperate column which retains the previous status. I have a small version of my spreadsheet and would love it if someone could help me with this.

Many thanks in advance

 

[Lutfor Rahman Shimanto]

Hi
I have a spreadsheet and I want to create a timestamp when the status is Open and a seperate timestamp when the status is closed. The timestamp I have created works except when the status moves from Open to closed the Timestamp that was created when it was first opened disappears and I need it to remain in place. Feel this can be achieved either by updating my formula or by adding a seperate column which retains the previous status. I have a small version of my spreadsheet and would love it if someone could help me with this.

Many thanks in advance

Hello Bradbu

Thanks for sharing such an exciting problem. I have reviewed the problem and your attached file and found that using an Excel VBA event procedure will suit your situation. Please check the following:

​

Follow these steps:

1. Right-click on the sheet name.
2. Click on View Code.
3. Paste the following code in the sheet module and save it:
   

   Private Sub Worksheet_Change(ByVal Target As Range)
   
       Dim cell As Range
       Dim ws As Worksheet
       Set ws = ThisWorkbook.Sheets("Timestamp")
       
       If Not Intersect(Target, ws.Range("E6:E12")) Is Nothing Then
   
           If LCase(Target.Value) = "open" Then
               ws.Cells(Target.Row, "G").Value = Now
               ws.Cells(Target.Row, "I").ClearContents
           ElseIf LCase(Target.Value) = "closed" Then
               ws.Cells(Target.Row, "I").Value = Now
               ws.Cells(Target.Row, "G").ClearContents
           End If
           
       End If
       
   End Sub

4. Return to the sheet and make desired changes.

Hopefully, you will like the solution. I have also attached the solution workbook for better understanding. Good luck.

Regards
Lutfor Rahman Shimanto
Excel & VBA Developer
ExcelDemy