VBA & Macro Problems

Calculate Elapsed Time by Date and Value with an Excel VBA Macro

Maira MehtabMaira Mehtab Nov 14, 2026 868 views

Question details

Calculate elapsed time between the earliest and latest entries for each date and value, with formatted results and a grand total.

Calculate Elapsed Time by Date and Value with an Excel VBA Macro
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet contains dates, values, and time entries that need to be grouped and summarized using a macro.
Observed behavior
Each date-and-value group should show its maximum time minus its minimum time, and the elapsed results should be totaled and displayed in the required format.
Before you start

Confirm which columns contain dates, grouping values, and times, and decide where the results should appear. The example uses columns A, B, and C on the active sheet, assumes headers in row 1, and creates a separate summary sheet.

Solution 1Recommended

Create an Excel VBA summary of elapsed times

Use this macro in desktop Excel for Windows to calculate one elapsed time per date-and-value group and sum those results.

The macro reads Excel date and time values rather than text, writes results to a new sheet, and leaves the source sheet's formatting unchanged. It uses a Windows scripting dictionary to group the rows.

The grand total is the sum of the individual group durations, not the difference between the earliest and latest times across the entire dataset. The macro compares time-of-day values within each date, so it is not suitable for shifts that cross midnight.

1
Check the source columns

On the worksheet, place dates in column A, grouping values in column B, and times in column C, with data starting in row 2. Adjust the column references in the code if your layout differs.

2
Add a VBA module

Press Alt+F11 to open the Visual Basic Editor, then select Insert > Module.

3
Paste the macro

Paste this code into the module: Sub SummarizeElapsedTimes() Dim src As Worksheet, result As Worksheet Dim groups As Object, item As Variant, k As Variant Dim lastRow As Long, r As Long, outRow As Long Dim d As Double, t As Double, total As Double Dim groupValue As String, key As String Set src = ActiveSheet Set groups = CreateObject("Scripting.Dictionary") lastRow = src.Cells(src.Rows.Count, "A").End(xlUp).Row For r = 2 To lastRow If Not IsEmpty(src.Cells(r, "A")) And _ Not IsEmpty(src.Cells(r, "B")) And _ Not IsEmpty(src.Cells(r, "C")) Then If IsNumeric(src.Cells(r, "A").Value2) And _ IsNumeric(src.Cells(r, "C").Value2) Then d = Int(CDbl(src.Cells(r, "A").Value2)) t = CDbl(src.Cells(r, "C").Value2) t = t - Int(t) groupValue = CStr(src.Cells(r, "B").Value2) key = CStr(d) & "|" & groupValue If Not groups.Exists(key) Then groups.Add key, Array(d, groupValue, t, t) Else item = groups(key) If t < item(2) Then item(2) = t If t > item(3) Then item(3) = t groups(key) = item End If End If End If Next r If groups.Count = 0 Then MsgBox "No valid date, value, and time rows were found." Exit Sub End If Set result = Worksheets.Add(After:=Worksheets(Worksheets.Count)) result.Range("A1:E1").Value = Array("Date", "Value", "Earliest", "Latest", "Elapsed") outRow = 2 For Each k In groups.Keys item = groups(k) result.Cells(outRow, 1).Value = item(0) result.Cells(outRow, 2).Value = item(1) result.Cells(outRow, 3).Value = item(2) result.Cells(outRow, 4).Value = item(3) result.Cells(outRow, 5).Value = item(3) - item(2) total = total + item(3) - item(2) outRow = outRow + 1 Next k result.Cells(outRow, 4).Value = "Grand total" result.Cells(outRow, 5).Value = total result.Columns("A").NumberFormat = "yyyy-mm-dd" result.Columns("C:D").NumberFormat = "hh:mm:ss" result.Columns("E").NumberFormat = "[h]:mm:ss" result.Columns("A:E").AutoFit End Sub

4
Run and check the summary

Return to the source worksheet, press Alt+F8, select SummarizeElapsedTimes, and click Run. Check the new sheet's group results and grand total against a few source rows.

5
Save the macro

Select File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm) if you want to keep the VBA code in the workbook.

Create an Excel VBA summary of elapsed times
Adapt the output if needed: This example creates a new summary sheet and writes calculated values, not formulas. If your workbook has multiple existing elapsed-time columns or requires specific cell formatting, adjust the output references and number formats before running it.
Calculate elapsed time without VBA

Summarize elapsed times in WPS Spreadsheets

Use a PivotTable to find the earliest and latest time for each date and value, then subtract them and sum the group durations in WPS Spreadsheets.

  1. 1. Open the data: Open the workbook in WPS Spreadsheets and select the table containing separate date, value, and time columns.
  2. 2. Create a PivotTable: Select Insert > PivotTable and place the PivotTable on a new worksheet.
  3. 3. Set up the groups and times: Add Date and Value to the row fields. Add Time twice to the values area, then use the value field settings to summarize one as Minimum and the other as Maximum.
  4. 4. Calculate elapsed time: In a column beside the PivotTable, enter a formula that subtracts the Minimum time cell from the Maximum time cell for each date-and-value row. Fill the formula down and exclude subtotal or grand-total rows.
  5. 5. Format and total the results: Select the elapsed cells, open Format Cells, and apply the custom format [h]:mm:ss. Use a SUM formula to total the individual group durations.
Group time entries by date and value without writing a macro.Display elapsed durations over 24 hours with a custom time format.Open and save Microsoft Excel formats such as .xlsx.
microsoft office alternative - wps office

Frequently Asked Questions

Why does my elapsed-time total show only the hours after 24?

A standard time format can wrap at 24 hours. Select the elapsed cells, open Format Cells > Number > Custom, and use [h]:mm:ss to display accumulated hours.

Does the macro handle a shift that crosses midnight?

No. It groups rows by date and compares only their time-of-day portions. For an overnight shift, use complete start and end date-time values and subtract the start from the end instead.

Why are some rows missing from the summary?

The macro skips rows with blank date, value, or time cells and rows whose date or time is not stored as a numeric Excel value. Check columns A and C and convert text entries to actual dates or times before running it again.

Can I write results into my existing elapsed-time columns?

Yes, but the example must be adapted. In the VBA editor, change the output cell references to match the intended columns and apply the required number formats; first identify which elapsed-time column should receive each result.

Will the macro remain in an .xlsx file?

No. To retain the code, use File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm). Enable macros only when you trust the workbook and its code.