# How to extract the time from a date and time in Excel

Keep the fraction of the day: `=MOD(A2,1)`, formatted as a time.

**Excel:** `=MOD(A2,1)` (Every current version)

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

## Example

Sample data (row 1 is the header):

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

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

Result: 8:15 AM; 5:40 PM; 12:05 AM; 11:59 PM; 12:00 PM

## How it works

A date and time is a whole number of days plus a fraction for the time. `MOD(A2,1)` keeps the remainder after dividing by 1, which is the fraction: the time of day. Format the result as a time to see it.

## Other ways

- `=A2-INT(A2)`: Subtracts the date part; the same result.
- `=TEXT(A2,"h:mm AM/PM")`: The time as text. (Text.)

## Common mistakes

- The result is a fraction like 0.34 until you format it as a time.
- For text timestamps, TIMEVALUE returns the time part: `=TIMEVALUE(A2)`.

## Questions

**How do I get just the date?**

`=INT(A2)`.

**How do I count timestamps before noon?**

`=SUMPRODUCT(--(MOD(A2:A6,1)<0.5))`.

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/extract-the-time-from-a-date-and-time