I have this code that works great. When I click on the macro "TimeSpanCheck"
Code:
Sub TimeSpanCheck()

Dim Break               As Long
Dim Prompt              As String
Dim Title               As String
Dim StartTime           As Date
Dim EndTime             As Date
Dim Duration            As Date
Dim Cel                 As Range

    If ActiveSheet Is Nothing Then
        Beep
        GoTo ExitSub:
    End If

    Prompt = "Select the start time."
    Title = "Start Time Specification"
    On Error Resume Next
    Set Cel = Application.InputBox(Prompt, Title, , , , , , 8)
    On Error GoTo 0
    If Cel Is Nothing Then
        GoTo ExitSub:
    End If
    StartTime = CDate(Cel.Value)

    Prompt = "Select the end time."
    Title = "End Time Specification"
    On Error Resume Next
    Set Cel = Application.InputBox(Prompt, Title, , , , , , 8)
    On Error GoTo 0
    If Cel Is Nothing Then
        GoTo ExitSub:
    End If
    EndTime = CDate(Cel.Value)

    Prompt = "Enter the break time in minutes to deduct."
    Title = "Break Time Specification"
    Break = Val(InputBox(Prompt, Title, 60))
    If Break < 0 Then
        Break = 0
    End If

    If EndTime > StartTime Then
        Duration = EndTime - StartTime - TimeSerial(0, Break, 0)
    Else
        Duration = EndTime - StartTime - TimeSerial(0, Break, 0) + 1
    End If

    Prompt = "The duration is: " & Format(Duration, "h:mm")
    Title = "Calcuation Results"
    MsgBox Prompt, vbInformation, Title

ExitSub:

    Set Cel = Nothing

End Sub
However it prompt me for the start time when I usually start this with the active cell that I am in and I have to click in the cell again.

So I attempted to modify my code and have the first input box start up with the active cell value (Time Value). I got it to work however it is giving me the wrong time value. If I go back to the original one it is correct. I believe it has to do with perhaps "StartTime = CDate(Cel.Value)" converting the input cell value. the original is reading the time value.

Here is my code not calculating correctly.
Code:
Sub ActiveCellTimeSpanCheck()

Dim Break               As Long
Dim Prompt              As String
Dim Title               As String
Dim StartTime           As Date
Dim EndTime             As Date
Dim Duration            As Date
Dim Cel                 As Range

    If ActiveSheet Is Nothing Then
        Beep
        GoTo ExitSub:
    End If
    
    Prompt = "Select the start time."
    Title = "Start Time Specification"
    On Error Resume Next
    ActiveCell = CDate(Cel.Value)
    'Want to always start with the active Cell
    Set Cel = Application.InputBox(Prompt, Title, CDate(ActiveCell.Value))
    'My original line : Set Cel = Application.InputBox(Prompt, Title, , , , , , 8)' works great but have to select first
'    On Error GoTo 0
'    If Cel Is Nothing Then
'        GoTo ExitSub:
'    End If
'    StartTime = CDate(Cel.Value)
    
    Prompt = "Select the end time."
    Title = "End Time Specification"
    On Error Resume Next
    Set Cel = Application.InputBox(Prompt, Title, , , , , , 8)
    On Error GoTo 0
    If Cel Is Nothing Then
        GoTo ExitSub:
    End If
    EndTime = CDate(Cel.Value)
    
    Prompt = "Enter the break time in minutes to deduct."
    Title = "Break Time Specification"
    Break = Val(InputBox(Prompt, Title, 60))
    If Break < 0 Then
        Break = 0
    End If
    
    If EndTime > StartTime Then
        Duration = EndTime - StartTime - TimeSerial(0, Break, 0)
    Else
        Duration = EndTime - StartTime - TimeSerial(0, Break, 0) + 1
    End If
    
    Prompt = "The duration is: " & Format(Duration, "h:mm")
    Title = "Calcuation Results"
    MsgBox Prompt, vbInformation, Title

ExitSub:

    Set Cel = Nothing
    
End Sub
This code unlike the original give me the wrong time
Example original code Start time 8:00 AM End Time 5:00 PM minus an hour lunch result is 8:00 the modified code result is 16:00

anyone see what I am doing incorrectly Thanks