As an alternative, you could use (simpler) user defined functions.
Function GetStartTime(TimeRange As Range)
Dim Cell As Range
For Each Cell In TimeRange
If Cell <> "" Then
GetStartTime = TimeValue(Left(Cells(2, Cell.Column), InStr(Cells(2, Cell.Column), "-") - 1) & ":00:00")
Exit Function
End If
Next Cell
If IsEmpty(GetStartTime) Then GetStartTime = ""
End Function
Function GetFinishTime(TimeRange As Range)
Dim N As Long
For N = TimeRange.Count To 1 Step -1
If TimeRange(N) <> "" Then
GetFinishTime = TimeValue(Mid(Cells(2, TimeRange(N).Column), InStr(Cells(2, TimeRange(N).Column), "-") + 1) & ":00:00")
Exit Function
End If
Next N
If IsEmpty(GetFinishTime) Then GetFinishTime = ""
End Function
Function GetLunchTime(TimeRange As Range)
Dim Cell As Range
For Each Cell In TimeRange
If Cell = "LE" Then
GetLunchTime = TimeValue(Left(Cells(2, Cell.Column), InStr(Cells(2, Cell.Column), "-") - 1) & ":00:00")
Exit Function
ElseIf Cell = "LL" Then
GetLunchTime = TimeValue(Left(Cells(2, Cell.Column), InStr(Cells(2, Cell.Column), "-") - 1) & ":30:00")
Exit Function
End If
Next Cell
If IsEmpty(GetLunchTime) Then GetLunchTime = ""
End Function
Open the VBA editor by hitting Alt F11.
Insert a new module with Insert - Module
Paste in the above function
Go back to the sheet by hitting Alt F11.
In a V3, enter =GetStartTime(C3:T3) etc.
Remember to save the workbook as a macro enabled workbook .xlsm
Bookmarks