A cell like 2026-03-14 09:30:00 holds a date and a time together. To filter by day or sum by hour, you often need them in two columns. How you split it depends on one thing: is the cell a real date-time, or text that looks like one?
This guide shows both cases on six made-up rows and shows how we told them apart.
Tested in: Excel 2021 desktop (Home & Business 2021, Version 2608) with an English interface on Korean-language Windows. We did not test other Excel versions.
Real Date-Time or Text?
Our DateTime sheet has the same six moments twice. Column A (real_datetime) holds real date-times. Column E (text_datetime) holds text that looks the same. In our sheet the difference showed in the alignment: column A is right-aligned, like a number, and column E is left-aligned, like text.
Alignment can be changed by the person who built the sheet, so treat it as a hint. For text dates that arrive from a CSV file, see fixing dates after a CSV import.
Case 1: A Real Date-Time
Excel stores a date-time as one number. The whole part is the day, and the fraction is the time of day. So the date is the whole part and the time is what is left over.
In B2, enter:
=INT(A2)In C2, enter:
=A2-INT(A2)Before any formatting, our columns showed plain numbers:
For 2026-03-14 09:30:00, B showed 46095 and C showed 0.395833333. That is 9:30 as a fraction of a day.
Now format the columns. We set column B to the date format yyyy-mm-dd and column C to a time format (Format Cells > Custom). The time format we applied displays 9:30:00, with no leading zero on the hour.
| Original | Date (B) | Time (C) |
|---|---|---|
2026-03-14 09:30:00 | 2026-03-14 | 9:30:00 |
2026-03-14 17:45:30 | 2026-03-14 | 17:45:30 |
2026-03-15 00:00:00 | 2026-03-15 | 0:00:00 |
2026-03-15 23:59:00 | 2026-03-15 | 23:59:00 |
2026-04-02 12:00:00 | 2026-04-02 | 12:00:00 |
2026-04-02 12:05:00 | 2026-04-02 | 12:05:00 |
The midnight row worked too: the time part was 0, shown as 0:00:00. We did not check whether a format is applied to B and C automatically when you enter the formulas.
Case 2: Text That Looks Like a Date-Time
If the cell is text, INT is not the right tool. Take the date and time pieces out of the text and convert each one. For text in E2 shaped like 2026-03-14 09:30:
=DATEVALUE(LEFT(E2,10))
=TIMEVALUE(MID(E2,12,8))LEFT(E2,10) takes the first ten characters, the date. MID(E2,12,8) takes up to eight characters starting at character 12, the time. This worked for both 09:30 and 17:45:30, with and without seconds. After we formatted the results as a date and a time, all six rows converted without an error.
Things to Know
- One text format. Our text dates were all in the form
yyyy-mm-dd hh:mm, tested on Windows with Korean regional settings. We did not test other text formats such as03/14/2026or other regional settings, so check your own data first. - Original data. The formulas sit in new columns, so columns
AandEare unchanged. - Formatting. The formulas return numbers. Apply a date format and a time format to see them as a date and a time. We did not test what the text-version results look like before formatting.
- Splitting by position. The text version relies on fixed positions. For splitting text around a character instead, see extracting text before or after a character.
One-line Summary
For a real date-time, =INT(A2) gives the date and =A2-INT(A2) gives the time; for text, use DATEVALUE on the first 10 characters and TIMEVALUE on the time part, then format the results.
Sources
- Note: every result on this page, including the serial numbers and the six converted rows, comes from our own reproduction in the Excel 2021 desktop app. We did not check these functions against Microsoft’s documentation for this guide.