Function Problems

Excel Formula to Subtract the First Timestamp from the Last

Rana GarciaRana Garcia Nov 14, 2026 868 views

Question details

Find the last numeric timestamp in a changing column and subtract a timestamp in a fixed starting row.

Subtract the First Timestamp from the Last in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet receives new timestamps daily in column AR, while the starting timestamp remains in AR6.
Observed behavior
The user wants a formula that calculates the elapsed time from AR6 to the last numeric timestamp without using a macro.
Before you start

Confirm that AR6 and the later timestamps are stored as Excel date or time values, not text. Make sure column AR contains no unrelated numeric values below the last timestamp, and place the formula outside column AR to avoid a circular reference.

Solution 1Recommended

Use LOOKUP to subtract AR6 from the last timestamp

Use this formula when new timestamps are added to column AR and the starting timestamp stays in AR6.

LOOKUP searches column AR for a numeric value using a very large search value. With ordinary timestamps in the column, it returns the last numeric value, from which the formula subtracts AR6. The dollar signs keep the references fixed if you copy the formula.

1
Select an output cell

Click an empty cell outside column AR where you want to display the elapsed time.

2
Enter the formula

Type =LOOKUP(9.9999999E+307,$AR:$AR)-$AR$6 and press Enter.

3
Format the elapsed time

Select the result cell, open Format Cells, and choose Custom. Enter [h]:mm:ss to display total elapsed hours, minutes, and seconds.

4
Check the result

Compare the result with AR6 and the last numeric timestamp in column AR. Add a new timestamp below the existing entries to confirm that the result updates.

Use LOOKUP to subtract AR6 from the last timestamp
Use dates for multi-day intervals: For intervals spanning midnight or multiple days, include both the date and time in the source cells. A time-only value does not identify which day it belongs to.
Calculate elapsed time in WPS Spreadsheets

Find the Last Timestamp with WPS Spreadsheets

Use the same LOOKUP formula in WPS Spreadsheets to calculate elapsed time as new timestamp rows are added.

  1. 1. Open the spreadsheet: Open WPS Spreadsheets and use Open to select the workbook containing the timestamps.
  2. 2. Check the source cells: Confirm that AR6 contains the starting date and time and that subsequent timestamps in column AR are numeric date or time values.
  3. 3. Enter the formula: Select an empty cell outside column AR, enter =LOOKUP(9.9999999E+307,$AR:$AR)-$AR$6, and press Enter.
  4. 4. Format the result: Open Format Cells for the result cell, choose a custom number format, and enter [h]:mm:ss to show total elapsed hours.
Find the last numeric timestamp without a macroDisplay elapsed durations with a custom time formatOpen and edit Microsoft Excel XLSX and XLS filesUse familiar spreadsheet formulas and controls
microsoft office alternative - wps office

Frequently Asked Questions

Why does the formula use 9.9999999E+307?

It is a very large number greater than ordinary Excel timestamps. LOOKUP uses it to return the last numeric value in column AR.

Why does the result look like a decimal instead of a duration?

Excel stores dates and times as numbers. Select the result cell, open Format Cells, and apply the custom format [h]:mm:ss to display the elapsed time.

What if my timestamps are stored as text?

LOOKUP needs numeric date or time values for this formula. Convert the text timestamps to recognized date and time values, then check that AR6 and the later entries are numeric.

Can I use a smaller range instead of the whole column?

Yes. For example, use =LOOKUP(9.9999999E+307,$AR$6:$AR$1000)-$AR$6. Make sure the range includes every row where a timestamp may be added.

Will the formula work across midnight?

It will calculate the interval correctly if the source cells contain both dates and times. Time-only entries do not indicate a change of day.