Grab only necessary data make new sheet

Try this

Option Explicit

Sub TidySheet()
    Dim n As Integer
    Dim RowNo As Long, LastRow As Long
    Dim ColNo As Long, LastCol As Long
    Dim rng As Range
    Dim arrData() As Variant
    
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    On Error GoTo ResetApplication
    
    Set rng = ActiveSheet.UsedRange
    LastRow = rng.Rows.Count
    LastCol = rng.Columns.Count
    
    Range("A:A").Clear
    Range("A1") = "Employee ID"
    Range("B1") = "Name of Employee"
    
    For RowNo = LastRow To 3 Step -1
        ReDim arrData(0)
        n = 0
        For ColNo = 1 To LastCol
            If Cells(RowNo, ColNo) <> "" Then
                arrData(n) = Cells(RowNo, ColNo)
                n = n + 1
                ReDim Preserve arrData(n)
            End If
        Next
        Select Case UBound(arrData)
            Case 0
                Rows(RowNo).Delete
            Case 3
                If Not IsNumeric(arrData(1)) Then
                    Rows(RowNo).Delete
                Else
                    Rows(RowNo).Clear
                    Cells(RowNo, "C") = arrData(0)
                    Cells(RowNo, "D") = arrData(1)
                    Cells(RowNo, "F") = arrData(2)
                    
                    If Cells(RowNo, "F") < 0.5 Then
                        Cells(RowNo, "E") = arrData(0) + 1
                    Else
                        Cells(RowNo, "E") = arrData(0)
                    End If
                End If
            Case 2
                If Not IsNumeric(arrData(1)) Then
                    Range("A2") = arrData(0)
                    Range("B2") = arrData(1)
                End If
                Rows(RowNo).Delete
            Case Else
                Rows(RowNo).Delete
        End Select
    Next
    Range("D:D,F:F").NumberFormat = "[$-F400]h:mm:ss AM/PM"
    Columns.AutoFit
    Rows.AutoFit

ResetApplication:
    Err.Clear
    On Error GoTo 0
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

It's a bit of a guess, but the best I can do with the sample.
N.B. it adds a day to the date where required

Hope this helps

[EDIT]

Just noticed I had overwritten the BU sheet in the sample sheet.