How to look up a date when the data has times in Excel
Formula (Excel and Google Sheets)
=XLOOKUP(G1,INT(A2:A9),B2:B9)Strip the times inside the lookup: =XLOOKUP(G1,INT(A2:A9),B2:B9). INT drops the time part of each timestamp.
- Result on the example
- 201 in
G2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in G2. Change any input and the result updates, or edit the formula to experiment.
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.
=XLOOKUP(G1,INT(A2:A9),B2:B9)Result201
G16/3/2026The value in G1.
A2:A98 rows × 1 col: 6/1/2026 7:30:00, 6/1/2026 16:30:00, 6/2/2026 8:15:00, 6/2/2026 17:15:00, …A range: 8 cells in one column.
INT(A2:A9){8 rows × 1 col} 46174, 46174, 46175, 46175, …INT: Rounds down to the nearest integer.- number
A2:A98 rows × 1 col: 6/1/2026 7:30:00, 6/1/2026 16:30:00, 6/2/2026 8:15:00, 6/2/2026 17:15:00, …
- number
B2:B98 rows × 1 col: 182, 175, 190, 188, …A range: 8 cells in one column.
XLOOKUP(G1,INT(A2:A9),B2:B9)201XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
G16/3/2026 - lookup_array
INT(A2:A9){8 rows × 1 col} 46174, 46174, 46175, 46175, … - return_array
B2:B98 rows × 1 col: 182, 175, 190, 188, …
- lookup_value
In Google Sheets
The same formula works in Google Sheets: =XLOOKUP(G1,INT(A2:A9),B2:B9). We ran it in Google Sheets mode on the same data and it returns 201.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =XLOOKUP(1,(A2:A9>=G1)*(A2:A9<G1+1),B2:B9) | 201 | 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)) | 201 | 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
--A2or DATEVALUE plus TIMEVALUE first.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Look up a date when the data has times
G2, return the first reading taken on the date in G1.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).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.