How to Separate Date and Time in Excel

What this does

A date-time in Excel is one number: the whole part is the date, the fraction is the time. Formatting a cell as a date hides the time but does not remove it, which is why two cells that both display 13/04/2026 can still fail an equality test and why filtering or grouping by date splits one day into dozens of entries. =INT(A2) truncates the fraction and returns a real date with a time of 00:00. Its counterpart, =A2-INT(A2), returns just the time. For a permanent change without a helper column, copy the INT results and Paste Special ▸ Values over the original. 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 “separate date and time in excel”, 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 13/04/2026 14:37, stored as 46125.6090. =INT(A2) returns 46125, which formatted as a date reads 13/04/2026 with no time. =A2-INT(A2) returns 0.6090, formatted as a time it reads 14:37. This is why =A2=DATE(2026,4,13) returns FALSE for the original but TRUE for the INT version — and why a PivotTable grouped on the raw column shows a separate row for every timestamp. Timestamps from exports and databases break every date comparison, filter and PivotTable grouping in the same invisible way, and one INT column fixes all of them. One habit worth forming early: name the cells that hold your inputs, so the formula reads in plain language instead of a string of cell addresses. A reviewer — or you in three months — can then follow the logic without decoding what B7 and D2 were supposed to mean, which is most of what makes a sheet maintainable.

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. The aim was to get you unstuck fast and leave you a little more capable than a copy-paste would. The answer is at the top and the detail above shows why it holds — so the next time a colleague asks, you can answer without reaching for search. If you take one thing from this page on “separate date and time in excel”, make it the habit rather than the keystrokes: set the problem up with labelled inputs, reference those cells, and let Excel do the recomputing. Bookmark the page for the syntax, but do the example once in a blank sheet and check it against the tool above — five minutes of hands-on practice fixes the method in memory far better than re-reading, and it surfaces the small snags while they are still harmless. After that the technique is genuinely yours: faster than searching for it again, and reliable enough to drop into work that other people depend on.

Common mistakes

• Applying a date-only format and assuming the time is gone; it is still there and still breaks comparisons.
• Using TEXT when the result has to keep behaving as a date — TEXT returns a string.
• Rounding with ROUND instead of truncating with INT, which pushes any time after midday to the next day.
• Comparing a raw date-time against a date and concluding the data is wrong when the difference is the hidden fraction.
• Forgetting to reformat the INT result, which then shows a five-digit serial number.

Frequently asked questions