# How to remove the time from a date in Excel

Use `=INT(A2)`. A timestamp is a date number plus a fraction for the time, and INT drops the fraction.

**Excel:** `=INT(A2)` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Timestamp | Date |
| 46083.34375 |  |
| 46083.73611111111 |  |
| 46084.00347222222 |  |
| 46086.99930555555 |  |
| 46090.5 |  |

Formula in B2, filled to B6: `=INT(A2)`

Result: 3/2/2026; 3/2/2026; 3/3/2026; 3/5/2026; 3/9/2026

## How it works

A timestamp is a date number plus the fraction of a day that has passed: 8:15 AM is 0.34. INT drops the fraction and leaves the date at midnight, so the results match plain dates in lookups, COUNTIFS and pivot tables.

## Other ways

- `=TRUNC(A2)`: The same for dates; INT and TRUNC only differ for negative numbers.
- `=ROUND(A2,0)`: A mistake to avoid: times from noon on round up to the next day. (Rounds 12:00 PM and later to the next day.)

## Common mistakes

- Changing the number format only hides the time. `=A2=DATE(2026,3,2)` is still FALSE, and lookups still miss.
- Timestamps imported as text need converting: `=DATEVALUE(A2)` returns the date and ignores the time.

## Questions

**How do I keep only the time instead?**

`=MOD(A2,1)` returns the fraction; format it as a time.

**How do I count the rows for one day when the column has times?**

Use a one-day window: `=COUNTIFS(A2:A6,">="&D1,A2:A6,"<"&D1+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/remove-the-time-from-a-date