In Excel: =A2-INT(A2) or =MOD(A2,1) strips the date portion, leaving the time as a decimal fraction of a day — format the result as a time (Ctrl+1 ▸ Number ▸ Time) or it displays as a small decimal like 0.65625 instead of 3:45 PM; =TIME(HOUR(A2),MINUTE(A2),SECOND(A2)) works too, rebuilding a clean time value from the original's hour/minute/second parts. To pull the DATE half instead, use =INT(A2).
On this page7
In an empty column, enter =A2-INT(A2) (or the equivalent =MOD(A2,1)) referencing the datetime cell.
Select that new cell, press Ctrl+1, and under Number choose Time with the format wanted (e.g. h:mm AM/PM) — otherwise it displays as a raw decimal.
For the date half instead, use =INT(A2) and format it as a Date.
If a rebuilt time value is preferred over a subtraction result, use =TIME(HOUR(A2),MINUTE(A2),SECOND(A2)) instead — it returns the same value via a different route.
Copy the formula down the column once it is confirmed correct for one row.
What this does
A datetime is stored internally as a single number: the integer part counts days since Excel's epoch, and the decimal part is the fraction of a 24-hour day already elapsed — 0.5 is noon, 0.75 is 6 PM. Extracting the time means isolating that decimal fraction and discarding the integer day count; A2-INT(A2) subtracts the whole-day part, and MOD(A2,1) reaches an identical result using the remainder-after-division-by-1 operator. Either formula returns a genuinely numeric time value that Excel still shows as a raw decimal until it is formatted as a time — the underlying number never changes, only how it displays. Keep the inputs visible and clearly labelled and the whole thing stays auditable — anyone who opens the file later, including you, can see at a glance exactly what feeds the result and change one assumption without hunting through the formula. For “excel extract time from datetime”, the reliable version is a short checking loop, not just the first command that appears to work. Run it on a deliberately small range first, watch how the affected cells change, and only then apply the same setup to the full sheet. When a formula is involved, keep the inputs labelled beside it, reference cells instead of typing values, and apply number formatting only after the result checks out. That is what makes a workflow that saves repeating the same clicks every week useful in real work: repeatable, auditable, and not dependent on memory or luck.
A worked example
A2 holds 8/12/2026 3:45 PM, stored internally as roughly 46246.65625. =A2-INT(A2) returns 0.65625 — formatted as a time via Ctrl+1 ▸ Number ▸ Time, that displays as 3:45 PM. Left in General format, the cell would instead show the bare decimal 0.65625, correct data but unreadable as a time. =INT(A2) returns 46246, which formatted as a date shows 8/12/2026 — the date part with the time stripped instead. Timestamps imported from a database, form response, or log file usually arrive as one combined datetime value, and separating the time — for a duration calculation, a schedule, or a chart — is a near-daily need once any real timestamped data enters a sheet. A practical tip before you scale it up: build it once on a small block of test data, confirm the number against the tool on this page, and only then point it at your real sheet. That one habit catches almost every mistake while it is still cheap to fix, long before a wrong figure reaches a report or a colleague.
In Google Sheets
Everything above works in Google Sheets too. Excel and Sheets share the formula syntax used here; only the surrounding menus are arranged differently. That portability is deliberate — learn it once and it follows you between the two tools and across Windows and Mac. Nothing on this page is behind a login or a download: the exact answer is at the top, and the detail below it is there for when you need it. That is the whole promise here — the answer first, and just enough context to make it stick. Treat “excel extract time from datetime” as a small building block rather than a chore. Once the inputs sit in their own cells and the formula reads from them, the same setup answers a dozen related questions with a tweak, and Excel keeps every dependent figure current as the data changes. The tool above is there so you can rehearse and verify before committing anything to a real workbook; the steps and worked example are there so the logic sticks. Get it right once and it stops costing you time — it starts saving it, every time the question comes back around.
Common mistakes
- Using
=A2-INT(A2)and leaving the result in General format, so it displays as a small decimal like 0.65625 instead of a readable time — the VALUE is correct, only the format is missing. - Reaching for
=TEXT(A2,"h:mm AM/PM")when a genuinely numeric time value is needed for further calculation — TEXT returns text, which cannot be added, subtracted, or compared as a time. - Assuming INT(
A2) only changes the DISPLAY — it genuinely truncates the value to the whole day in that new cell, discarding the time fraction for good there. - Confusing this with removing the time from the ORIGINAL cell's display (a number-format job) rather than extracting it into a separate cell (a formula job) — the two solve different problems.
Frequently asked questions
What is the formula to extract just the time from a date-and-time value in Excel?
=A2-INT(A2) or =MOD(A2,1), then format the result as a time so it displays correctly instead of as a decimal.
How do I extract just the date and drop the time?
=INT(A2) truncates a datetime to its whole-day integer, which formats as the date alone.
Does this work in Google Sheets too?
Yes — Sheets stores datetimes the same way (a day count plus a day-fraction), so =A2-INT(A2), =MOD(A2,1), and =INT(A2) all return identical results and need the same manual time/date number format applied via Format ▸ Number.
Other ways people ask this
On the way here you may have searched this as “how to extract time from datetime in excel” and “excel get time from datetime” — it is all the same task, and this page is the single, complete answer to it.
Why do people search for this in so many different ways?
Because the same task has many names. “how to extract time from datetime in excel”, “excel get time from datetime” all point at the one operation explained on this page, which is why they all lead here.