Excel Formula to Subtract the First Timestamp from the Last
Question details
Find the last numeric timestamp in a changing column and subtract a timestamp in a fixed starting row.

- 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.
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.
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.
Click an empty cell outside column AR where you want to display the elapsed time.
Type =LOOKUP(9.9999999E+307,$AR:$AR)-$AR$6 and press Enter.
Select the result cell, open Format Cells, and choose Custom. Enter [h]:mm:ss to display total elapsed hours, minutes, and seconds.
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.

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. Open the spreadsheet: Open WPS Spreadsheets and use Open to select the workbook containing the timestamps.
- 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. Enter the formula: Select an empty cell outside column AR, enter =LOOKUP(9.9999999E+307,$AR:$AR)-$AR$6, and press Enter.
- 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.

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.




