This software uses a GPLv3 Software License, if you are unfamiliar with the terms of this license check the attachments to this file.




'Start the Program

    Sub Logistics_Macros_Weekly_Stats()







'Generate a range of two columns to compare the times of, if the second day is earlier (smaller) than the

'first day then add 24 hours to it (it is on the following day)

    Dim TwoColumns As Range, i As Integer

    Set TwoColumns = Selection

    With TwoColumns

        For i = 2 To 700

        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







'Get the times in the C column into the right time format

    Columns("C:C").Select

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

    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







'Get the times in the D column into the right time format

    Columns("D:D").Select

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

    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







'Format the Time Difference column and copy to the formula to the bottom of the column

    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:M700")

    Range("M2:M380").Select

    Range("O16").Select







'Delete all extra zeros in the M column

    Dim MyRangeDeleteZero As Range

    Dim MyCellDeleteZero As Range

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

    For Each MyCellDeleteZero In MyRange

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

    MyCellDeleteZero.Value = ""

    End If

    Next MyCellDeleteZero







'Generate a range for two columns, on the same row if the c column is blank then clean up the m column by deleting the zero

    Dim TwoColumnsZero As Range, T As Integer

    Set TwoColumnsZero = Selection

    With TwoColumnsZero

        For T = 2 To 700

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

        Cells(T, 13).Value = ""

    End If

    Next T

    End With







'Format the Time Difference column to fit the data

    ActiveWindow.SmallScroll Down:=-228

    Range("M1").Select

    ActiveWindow.SmallScroll Down:=-63

    ActiveCell.FormulaR1C1 = "Time Difference"

    Range("M2").Select

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







'Enter the title "Entry Hours" into the N column and format the column 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:N500"), Type:=xlFillDefault

    Range("N2:N500").Select

    Range("N201").Select

    Selection.ClearContents

    ActiveWindow.SmallScroll Down:=-483







'Generate a range for two columns, on the same row if the C column is blank then delete the value in the N column

   Dim TwoColumnsN As Range, i2 As Integer

    Set TwoColumnsN = Selection

    With TwoColumnsN

        For i2 = 2 To 700

        If Cells(i2, 3) = "" Then

        Cells(i2, 14).Value = ""

    End If

    Next i2

    End With







'Enter the term "Daily Hours" title into cell P1 and format the 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

    Range("Q1").Select

    ActiveCell.FormulaR1C1 = "Total Trucks by Hour"







'Enter the term "Weekly Total of Logistic Trucks by Hour" inot cell Q1 and format the column

'to include all hours between 0 and 23 (12 AM and 11 PM)

    ActiveCell.FormulaR1C1 = "Weekly Total of 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







'Enter the term "Weekly Average Number of Logistic Trucks" into cell R2 and format the column to fit the data

    Range("R1").Select

    ActiveCell.FormulaR1C1 = "Weekly Average Number of Logistic Trucks by Hour"

    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 "Weekly Average Time of Logistic Trucks' Trips by Hour" column to fit the data

    Range("S1").Select

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

    Range("S2").Select

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

'Format the S column to fit the data and format the time to the right format

    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 format the time to the right format

    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;@"







'If there is an error message in a cell in the range S2 through S25 change it to "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







'If there is an error message in a cell in the range S2 through S25 change it to "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"

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







'Format the "Weekly Average Time Logistic Trucks' column to fit the data

    Range("T1").Select

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

    Range("T2").Select

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







'Format the "Weekly Average Time Logistic Trucks" colum so that the times are in the right format, also

'if the tme is zero in the S column do not include it in the "Weekly Average Time Logistic Trucks"

    Range("T1") = "Weekly Average Time of Logistic Trucks'  by Hour"

    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;@"







'End the Program

    End Sub