Automatic generation of Start time, end time and total time in excel

Hi,

One way would be with the following two Sheet event macros. First protect the sheet then click in column A to record the Start Time, and right click in column A to record the end time. Column D is used as a test indicator.

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        ActiveSheet.Unprotect
        If Range("D" & ActiveCell.Row) = "Started" Then
            Range("C" & ActiveCell.Row) = Time
        End If
        Range("D" & ActiveCell.Row) = "Finished"
        ActiveSheet.Protect
    End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        ActiveSheet.Unprotect
        If Range("D" & ActiveCell.Row) <> "Started" Then
            Range("B" & ActiveCell.Row) = Time
        End If
        Range("D" & ActiveCell.Row) = "Started"
        ActiveSheet.Protect
    End If
End Sub

Simpler, simply track the selection of cells in column B and C and enter a timestamp into the cells clicked. When a timestamp is entered into C, a calculation goes into D as well.

Something like this should get you started:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Me.Unprotect
With Target.Cells(1)
    Select Case .Column
        Case 2          'column B selected
            If Range("A" & .Row) <> "" And .Value = "" Then .Value = Now
        
        Case 3          'column C selected
            If Range("A" & .Row) <> "" And Range("B" & .Row) <> "" And .Value = "" Then
                .Value = Now
                .Offset(, 1).Value = .Value - .Offset(, -1).Value
            End If
    End Select
End With
Me.Protect
End Sub

You can click on as many column B cells as you wish without clicking column C to end a previous one, it will keep starting new rows as you do.

When you finally click a column C cell, it will close that row only.