# How to look up a date when the data has times in Excel

Strip the times inside the lookup: `=XLOOKUP(G1,INT(A2:A9),B2:B9)`. INT drops the time part of each timestamp.

**Excel:** `=XLOOKUP(G1,INT(A2:A9),B2:B9)` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| Timestamp | Reading |  |  |  | Date | 2026-06-03 |
| 46174.3125 | 182 |  |  |  | First reading |  |
| 46174.6875 | 175 |  |  |  |  |  |
| 46175.34375 | 190 |  |  |  |  |  |
| 46175.71875 | 188 |  |  |  |  |  |
| 46176.3125 | 201 |  |  |  |  |  |
| 46176.625 | 197 |  |  |  |  |  |
| 46177.3125 | 205 |  |  |  |  |  |
| 46177.6875 | 199 |  |  |  |  |  |

Formula in G2: `=XLOOKUP(G1,INT(A2:A9),B2:B9)`

Result: 201

## How it works

A timestamp is a date plus a fraction for the time: June 3, 2026 at 7:30 AM is 46176.3125, while the date alone is 46176. An exact lookup never finds the plain date. `INT(A2:A9)` drops the fraction from every timestamp, so XLOOKUP compares dates with dates and returns the first reading that day.

## Other ways

- `=XLOOKUP(1,(A2:A9>=G1)*(A2:A9<G1+1),B2:B9)`: Tests for times from midnight up to, but not including, the next midnight. Useful when you also need a time window.
- `=INDEX(B2:B9,MATCH(G1,INT(A2:A9),0))`: For Excel 2019 and earlier (Ctrl+Shift+Enter in 2016 and older).

## Common mistakes

- A timestamp formatted to show only the date still has its time underneath. Check by formatting it as a number.
- Timestamps imported as text don't work with INT. Convert them with `--A2` or DATEVALUE plus TIMEVALUE first.

## Questions

**How do I get the last reading of the day?**

Search from the bottom: `=XLOOKUP(G1,INT(A2:A9),B2:B9,,0,-1)`.

**How do I add up the readings for the day?**

Use a date range in SUMIFS: `=SUMIFS(B2:B9,A2:A9,">="&G1,A2:A9,"<"&G1+1)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/lookup-date-with-times