Automating Order Time Tracking in Excel with VBA

Person typing on laptop

In the fast-paced world of logistics and order management, accurately tracking time spent on each order is crucial. Manually entering start, pause, and stop times can be error-prone and time-consuming. Fortunately, Excel VBA offers a powerful solution to automate this process.

The Problem: Manual Time Tracking is Inefficient

Manually tracking order processing times with start, pause, and stop entries can lead to errors and wasted time. What if there was a way to automate this process in Excel using VBA?

Why This Happens:

Human Error: Manual data entry is prone to mistakes.
Time Consuming: Manually entering times for multiple orders can take up valuable time.
Inconsistent Data: Different users may enter data in slightly different formats, leading to inconsistencies.

Step-by-Step Solution: Automating Order Time Tracking with VBA

Example Scenario 1: Basic Start and Pause Automation

Let’s start with a basic example where you want to track the time spent on an order, excluding pause times.

Sub TrackOrderTime()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Orders")

    ' Check if the current cell contains an order number
    If IsNumeric(ActiveCell.Value) Then
        ' Enter start time in column B (next to the selected order)
        If ws.Cells(ActiveCell.Row, 2).Value = "" Then
            ws.Cells(ActiveCell.Row, 2).Value = Now
        ElseIf ws.Cells(ActiveCell.Row, 3).Value = "Paused" Then
            ' Enter pause time in column C (next to the selected order)
            ws.Cells(ActiveCell.Row, 4).Value = Now
            ws.Cells(ActiveCell.Row, 3).Value = ""
        Else
            ' Calculate total duration excluding pauses and update Column D
            Dim startTime As Date, endTime As Date, pauseStart As Date, pauseEnd As Date
            startTime = ws.Cells(ActiveCell.Row, 2).Value
            endTime = Now()
            If Not IsEmpty(ws.Cells(ActiveCell.Row, 4).Value) Then
                pauseStart = ws.Cells(ActiveCell.Row, 4).Value
                pauseEnd = Now()
                ws.Cells(ActiveCell.Row, 5).Value = ws.Cells(ActiveCell.Row, 5).Value + (pauseEnd - pauseStart)
            End If

            ' Calculate total duration and update Column E
            Dim totalTime As Double
            totalTime = endTime - startTime - ws.Cells(ActiveCell.Row, 5).Value
            ws.Cells(ActiveCell.Row, 6).Value = totalTime
        End If
    Else
        MsgBox "Please select a cell with an order number."
    End If
End Sub

Example Scenario 2: Handling Multiple Pauses

In this scenario, we’ll extend our solution to handle multiple pauses and restarts.

Sub TrackOrderTimeWithPauses()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Orders")

    ' Check if the current cell contains an order number
    If IsNumeric(ActiveCell.Value) Then
        ' Enter start time in column B (next to the selected order)
        If ws.Cells(ActiveCell.Row, 2).Value = "" And ws.Cells(ActiveCell.Row, 3).Value = "" Then
            ws.Cells(ActiveCell.Row, 2).Value = Now

        ElseIf ws.Cells(ActiveCell.Row, 3).Value = "Paused" Then
            ' Enter pause time in column C (next to the selected order)
            Dim previousPause As Double
            If Not IsEmpty(ws.Cells(ActiveCell.Row, 4).Value) Then
                previousPause = ws.Cells(ActiveCell.Row, 5).Value + Now - ws.Cells(ActiveCell.Row, 4).Value
            Else
                previousPause = Now - ws.Cells(ActiveCell.Row, 2).Value
            End If

            ' Update total pause duration in Column E
            ws.Cells(ActiveCell.Row, 5).Value = previousPause + (Now - ws.Cells(ActiveCell.Row, 4).Value)
            ws.Cells(ActiveCell.Row, 3).Value = ""
        Else
            Dim startTime As Date, endTime As Date
            startTime = ws.Cells(ActiveCell.Row, 2).Value
            endTime = Now()

            ' Calculate total duration excluding pauses and update Column D
            Dim totalPauseDuration As Double
            totalPauseDuration = ws.Cells(ActiveCell.Row, 5).Value

            ' Calculate total time worked on the order
            ws.Cells(ActiveCell.Row, 6).Value = endTime - startTime - totalPauseDuration
        End If
    Else
        MsgBox "Please select a cell with an order number."
    End If
End Sub

Example Scenario 3: Including Weekends and Holidays Exclusion

The final example will exclude weekends and holidays from the total time calculation.

Sub TrackOrderTimeWithHolidayExclusion()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Orders")

    ' Check if the current cell contains an order number
    If IsNumeric(ActiveCell.Value) Then
        ' Enter start time in column B (next to the selected order)
        If ws.Cells(ActiveCell.Row, 2).Value = "" And ws.Cells(ActiveCell.Row, 3).Value = "" Then
            ws.Cells(ActiveCell.Row, 2).Value = Now

        ElseIf ws.Cells(ActiveCell.Row, 3).Value = "Paused" Then
            ' Enter pause time in column C (next to the selected order)
            Dim previousPause As Double
            If Not IsEmpty(ws.Cells(ActiveCell.Row, 4).Value) Then
                previousPause = ws.Cells(ActiveCell.Row, 5).Value + Now - ws.Cells(ActiveCell.Row, 4).Value
            Else
                previousPause = Now - ws.Cells(ActiveCell.Row, 2).Value
            End If

            ' Update total pause duration in Column E
            ws.Cells(ActiveCell.Row, 5).Value = previousPause + (Now - ws.Cells(ActiveCell.Row, 4).Value)
            ws.Cells(ActiveCell.Row, 3).Value = ""
        Else
            Dim startTime As Date, endTime As Date
            startTime = ws.Cells(ActiveCell.Row, 2).Value
            endTime = Now()

            ' Calculate total duration excluding weekends and holidays
            Dim holidayDates As Variant
            holidayDates = Array("2023-12-25", "2024-01-01") ' Add your holiday dates here

            ' Function to calculate work days excluding holidays
            ws.Cells(ActiveCell.Row, 6).Value = CalculateWorkDays(startTime, endTime, holidayDates)
        End If
    Else
        MsgBox "Please select a cell with an order number."
    End If
End Sub

Function CalculateWorkDays(startDate As Date, endDate As Date, holidays As Variant) As Double
    Dim totalDays As Integer
    Dim i As Integer
    Dim holiday As String
    totalDays = 0

    For i = startDate To endDate
        ' Check if the date is a weekend or a holiday
        If Weekday(i, vbSunday)  vbSaturday And Weekday(i, vbSunday)  vbSunday Then
            ' Check against holidays array for exclusion
            Dim j As Integer
            For j = LBound(holidays) To UBound(holidays)
                If i = DateValue(holidays(j)) Then Exit For
            Next j

            If j > UBound(holidays) Then totalDays = totalDays + 1 ' Only count workdays
        End If
    Next i

    CalculateWorkDays = totalDays / 24 ' Convert to days as a decimal number for Excel
End Function

Using Tools for Enhanced Automation

While VBA is powerful, there are tools that can enhance and streamline this process even further. For instance, CelTools, offers a range of features for formula auditing, data analysis, and automation in Excel.

Advanced Variation: Using CelTools to Enhance Automation

For more complex time tracking needs or larger datasets, consider using CelTools. This toolset provides features for automating data entry, handling large datasets efficiently, and much more. CelTools can handle many of the repetitive tasks in your VBA code automatically.

Common Mistakes and Misconceptions

Not Checking Data Types: Always ensure that you’re checking if cells contain valid data before processing them to avoid errors.
Ignoring Weekends/Holidays: If your business requires excluding weekends and holidays, make sure to account for these in your time calculations.
Inconsistent Cell References: Ensure consistent cell references throughout your VBA code to avoid errors when running the macro multiple times.

Brief Technical Summary

The solution provided automates order time tracking by using VBA macros in Excel. The approach includes basic start and pause functionality, handling of multiple pauses, and exclusion of weekends and holidays from total time calculations. The use of tools like CelTools can further enhance automation for more complex datasets.

Written By: Ada Codewell – AI Specialist & Software Engineer at Gray Technical