'Start the Program

    Sub Logistics_Macros_Mon_Through_Sun()







'Generate the range that contains two columns and then compare them to see if the second column is larger than the first

'If the Second column is smaller than the first column then add 24 hours to it (it is on another day than the original time)

    Dim TwoColumns As Range, i As Integer

    Set TwoColumns = Selection

    With TwoColumns

        For i = 2 To 200

        If Cells(i, 4) < Cells(i, 3) Then

        'MsgBox ("Column D in" & Cells(i, 4) & "is smaller")

        Cells(i, 4).Value = Cells(i, 4) + 2400

    End If

    Next i

    End With







'Loop through all rows of column C and correct the time format of every cell in the column

    Columns("C:C").Select

    Set MyRange = Range("$C$2:$C$200")

    For Each MyCell In MyRange

    If MyCell.Value Like "???" Then

    Dim Three_Characters

    Three_Characters = MyCell.Value

    MyCell.Value = Left(Three_Characters, 1) & ":" & Right(Three_Characters, 2) & ":00"

    ElseIf MyCell.Value Like "????" Then

    Dim Four_Characters

    Four_Characters = MyCell.Value

    MyCell.Value = Left(Four_Characters, 2) & ":" & Right(Four_Characters, 2) & ":00"

    End If

    Next MyCell

    Columns("C:C").EntireColumn.AutoFit







'Select Collumn D and correct the time format of every cell in the column

    Columns("D:D").Select

    Set MyRange = Range("$D$2:$D$200")

    For Each MyCellD In MyRange

    If MyCellD.Value Like "???" Then

    Dim Three_CharactersD

    Three_CharactersD = MyCellD.Value

    MyCellD.Value = Left(Three_CharactersD, 1) & ":" & Right(Three_CharactersD, 2) & ":00"

    ElseIf MyCellD.Value Like "????" Then

    Dim Four_CharactersD

    Four_CharactersD = MyCellD.Value

    MyCellD.Value = Left(Four_CharactersD, 2) & ":" & Right(Four_CharactersD, 2) & ":00"

    End If

    Next MyCellD







'Generate the Time Difference column, subtract the second time column from the first time column to get the time to unload

    ActiveCell.FormulaR1C1 = "Time Difference"

    Range("M2").Select

    With Selection.Interior

        .Pattern = xlNone

        .TintAndShade = 0

        .PatternTintAndShade = 0

    End With

    ActiveCell.FormulaR1C1 = "=RC[-9]-RC[-10]"

    Range("M2").Select

    Selection.AutoFill Destination:=Range("M2:M500")

    Range("M2:M380").Select

    Range("O16").Select







'Generate ranges and use them to delete all unnecessary zeros in the M column

    Dim MyRangeDeleteZero As Range

    Dim MyCellDeleteZero As Range

    Set MyRangeDeleteZero = Range("$M$2:$M$200")

    For Each MyCellDeleteZero In MyRange

    If MyCellDeleteZero.Value = "00:00:00" Then

    MyCellDeleteZero.Value = ""

    End If

    Next MyCellDeleteZero







'Generate ranges and use them to delete all unnecessary zeros in the C column

    Dim TwoColumnsZero As Range, T As Integer

    Set TwoColumnsZero = Selection

    With TwoColumnsZero

        For T = 2 To 200

        If IsEmpty(Cells(T, 3).Value) Then

        Cells(T, 13).Value = ""

    End If

    Next T

    End With







'Format the Time Difference column so that data fits the column

    ActiveWindow.SmallScroll Down:=-228

    Range("M1").Select

    ActiveWindow.SmallScroll Down:=-63

    ActiveCell.FormulaR1C1 = "Time Difference"

    Range("M2").Select

    Columns("M:M").EntireColumn.AutoFit







'Generate the Entry Hours column and format it to fit the data

    Range("N1").Value = "Entry Hours"

    Range("N2").Select

    Columns("N:N").EntireColumn.AutoFit

    ActiveCell.FormulaR1C1 = "=HOUR(RC[-11])"

    Range("N2").Select

    Selection.AutoFill Destination:=Range("N2:N200"), Type:=xlFillDefault

    Range("N2:N200").Select

    Range("N201").Select

    Selection.ClearContents

    ActiveWindow.SmallScroll Down:=-483







'Generate a range and use it to delete unneccary zeros in the N column

   Dim TwoColumnsN As Range, i2 As Integer

   Set TwoColumnsN = Selection

   With TwoColumnsN

        For i2 = 2 To 200

        If Cells(i2, 3) = "" Then

        Cells(i2, 14).Value = ""

    End If

    Next i2

    End With







'Format the Daily Hours column to fit the data

    Range("P1").Value = "Daily Hours"

    Range("P2").Select

    Columns("P:P").EntireColumn.AutoFit

   ActiveCell.FormulaR1C1 = "0"

    Range("P3").Select

    ActiveCell.FormulaR1C1 = "1"

    Range("P2:P3").Select

    Selection.AutoFill Destination:=Range("P2:P25"), Type:=xlFillDefault

    Range("P2:P24").Select







'Generate the Total Trucks by Hour column and record the number of logistic trucks by the hour they arrive

    Range("Q1").Select

    ActiveCell.FormulaR1C1 = "Total Trucks by Hour"

    ActiveCell.FormulaR1C1 = "Total Logistic Trucks by Hour"

    Range("Q2").Select

    Columns("Q:Q").EntireColumn.AutoFit

    Range("Q2").Select

    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-3], ""0"")"

    Range("Q2").Select

    Selection.AutoFill Destination:=Range("Q2:Q25"), Type:=xlFillDefault

    Range("Q2:Q25").Select

    Range("Q3").Select

    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-3], ""1"")"

    Range("Q4").Select

    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-3], ""2"")"

    Range("Q5").Select

    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-3], ""3"")"

    Range("Q6").Select

    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-3], ""4"")"

    Range("Q7").Select

    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-3], ""5"")"

    Range("Q8").Select

    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-3], ""6"")"

    Range("Q9").Select

    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-3], ""7"")"

    Range("Q10").Select

    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-3], ""8"")"

    Range("Q11").Select

    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-3], ""9"")"

    Range("Q12").Select

    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-3], ""10"")"

    Range("Q13").Select

    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-3], ""11"")"

    Range("Q13").Select

    Selection.AutoFill Destination:=Range("Q13:Q25"), Type:=xlFillDefault

    Range("Q13:Q25").Select

    Range("Q14").Select

    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-3], ""12"")"

    Range("Q15").Select

    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-3], ""13"")"

    Range("Q16").Select

    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-3], ""14"")"

    Range("Q17").Select

    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-3], ""15"")"

    Range("Q18").Select

    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-3], ""16"")"

    Range("Q19").Select

    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-3], ""17"")"

    Range("Q20").Select

    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-3], ""18"")"

    Range("Q21").Select

    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-3], ""19"")"

    Range("Q22").Select

    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-3], ""20"")"

    Range("Q23").Select

    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-3], ""21"")"

    Range("Q24").Select

    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-3], ""22"")"

    Range("Q25").Select

    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-3], ""23"")"

    Range("Q26").Select







'Format the Daily Average Number of Logistic Trucks column to fit the data

    Range("R1").Select

    ActiveCell.FormulaR1C1 = "Daily Average Number of Logistic Trucks"

    Range("R2").Select

    Columns("R:R").ColumnWidth = 8.86

    Columns("R:R").EntireColumn.AutoFit

    ActiveCell.FormulaR1C1 = "=AVERAGE(R2C17:R25C17)"

    Range("R2").Select

    Selection.AutoFill Destination:=Range("R2:R25")

    Range("R2:R25").Select







'Format the Average Time of Logistic Trucks' Trip by Hour column to fit the data

    Range("S1").Select

    ActiveCell.FormulaR1C1 = "Average Time of Logistic Trucks' Trip by Hour"

    Range("S2").Select

    Columns("S:S").EntireColumn.AutoFit







'Format the time format in the S column

    Range("S2").Select

    Columns("S:S").EntireColumn.AutoFit

    ActiveCell.FormulaR1C1 = "=AVERAGEIF(C[-5],RC[-3],C[-6])"

    Range("S2").Select

    Selection.AutoFill Destination:=Range("S2:S25"), Type:=xlFillDefault

    Range("S2:S25").Select

    Selection.NumberFormat = "h:mm;@"







'Format the S column to fit the data and fill the S column with the formula in cell S2

    Range("S2").Select

    Columns("S:S").EntireColumn.AutoFit

    ActiveCell.FormulaR1C1 = "=AVERAGEIF(C[-5],RC[-3],C[-6])"

    Range("S2").Select

    Selection.AutoFill Destination:=Range("S2:S25"), Type:=xlFillDefault

    Range("S2:S25").Select

    Selection.NumberFormat = "h:mm;@"







'Format the S column to change all error messages to the time "0:00"

    Set MyRange4 = Range("S2:S25")

    For Each MyCell4 In MyRange4

    If IsError(MyCell4) Then

    MyCell4.Value = "0:00"

   End If

    Next MyCell4

    Range("$S$2:$S$25").Font.Name = "Calibri"

    Range("$S$2:$S$25").Borders(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlLineStyleNone







'Format the S column to change all erorro message to the time "0:00"

    Set MyRange5 = Range("S2:S25")

    For Each MyCell5 In MyRange4

    If IsError(MyCell5) Then

    MyCell5.Value = "0:00"

    End If

    Next MyCell5

    Range("$S$2:$S$25").Font.Name = "Calibri"

    Range("$S$2:$S$25").Borders(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlLineStyleNone







'Enter the title "Daily Average Time Logistic Trucks" into cell T1 and format the column to fit the data

    Range("T1").Select

    ActiveCell.FormulaR1C1 = "Daily Average Time Logistic Trucks"

    Range("T2").Select

    Columns("T:T").EntireColumn.AutoFit







'If there is a zero in the Daily Average Logistic Trucks column do not include it in the average for the day

    Range("T1") = "Daily Average Time Logistic Trucks"

    Range("T2").Select

    Columns("T:T").EntireColumn.AutoFit

    Range("T2").Select

    ActiveCell.FormulaR1C1 = "=AVERAGEIF(R2C19:R25C19, ""<> 0"")"

    Range("T3").Select

    ActiveWindow.ScrollColumn = 1

    ActiveWindow.SmallScroll ToRight:=9

    Range("T2").Select

    Selection.AutoFill Destination:=Range("T2:T25")

    Range("T2:T25").Select

    Selection.NumberFormat = "h:mm;@"







'Close the Program

    End Sub