Show Last Entry Time Under Each Date in Excel – A Step-by-Step Guide

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

The Problem with Tracking Last Entry Times in Excel

One of the most common challenges that users face when working with time-stamped data in Excel is displaying the last entry time under each date. Whether you’re tracking expenses, project progress, or any other type of activity, having a clear and accurate timestamp can be crucial for analysis and reporting.

The Solution: Step-by-Step Guide

Here’s how to display the last entry time under each date in Excel:

Step 1: Prepare Your Data

First, ensure your data is organized with dates and times. Typically, you’ll have two columns – one for dates and another for times.

Excel Spreadsheet with Dates and Times

Step 2: Add a Column for Last Entry Time

Create an additional column to store the last entry time. For example, if your date is in cell A1 and time in B1, add this formula in C1:

=IFERROR(INDEX(B:B,MATCH(MAX(IF(A:A=A1,B:B)),B:B,0)),"")

This formula finds the maximum (latest) time for each date and displays it.

Step 3: Make It Dynamic with VBA

For a more dynamic solution, use VBA. Press ALT + F11, insert a new module, and paste this code:

Sub AddLastEntryTime()
    Dim ws As Worksheet
    Set ws = ActiveSheet

    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    For i = 2 To lastRow
        If IsDate(ws.Cells(i, 1)) Then
            maxTime = Application.WorksheetFunction.Max(ws.Range("B:B"))
            ws.Cells(i, 3).Value = Application.WorksheetFunction.Index(ws.Columns(2), _
                Application.WorksheetFunction.Match(maxTime, ws.Columns(2), 0))
        End If
    Next i

End Sub

Run this macro to populate the last entry times in column C.

Alternative Approach: Using CelTools for Enhanced Functionality

While you can manually set up formulas or use VBA, tools like CelTools offer enhanced functionality. With CelTools, you can automate the entire process with a single click:

Advanced Variation: Handling Shifting Columns

If your columns shift often, use dynamic references in VBA or formulas to adjust automatically.

Sub AddLastEntryTimeWithDynamicColumns()
    Dim ws As Worksheet
    Set ws = ActiveSheet

    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    For i = 2 To lastRow
        If IsDate(ws.Cells(i, 1)) Then
            colNum = Application.WorksheetFunction.Match("Time", ws.Rows(1), 0)
            maxTime = Application.WorksheetFunction.Max(ws.Columns(colNum))
            ws.Cells(i, 3).Value = Application.WorksheetFunction.Index(ws.Columns(colNum), _
                Application.WorksheetFunction.Match(maxTime, ws.Columns(colNum), 0))
        End If
    Next i

End Sub

Common Mistakes and Misconceptions

The most common mistake is not handling empty cells correctly.
Be sure to account for date formats in your formulas or VBA code.
Avoid hardcoding column references if the layout may change frequently.

Technical Summary

In this article, we’ve explored how to display the last entry time under each date in Excel using both manual methods and automated solutions with VBA. For frequent users or those needing additional functionality, tools like CelTools offer a robust alternative that simplifies complex tasks.

By combining manual techniques with specialized tools, you can effectively manage and analyze time-stamped data in Excel. This approach not only ensures accuracy but also saves valuable time for more critical activities.

Team Working with Excel