You can split date and time in Excel in under five seconds by using simple formulas and a couple of clicks instead of manually retyping or editing each cell. In this article, you will learn three fast, fully automated methods: using the INT function, Flash Fill, and Power Query, plus a ready‑to‑use table example you can replicate in your own workbook.

Why splitting date and time matters

When your data combines date and time in one column, Excel still stores it as a single serial number, with the date as the whole part and the time as the decimal fraction. For example, 1/1/2023 4:15 AM is stored internally as 44927.177, so INT returns 44927, which displays as 1/1/2023 when formatted as a date. Separating them into two columns lets you filter just by date, analyse only time patterns, or build clean reports without manual rework.

Method 1: INT and subtraction (5–10 seconds)

Assume your combined date‑time values sit in column A, starting from A2.

Step 1 – Extract the date
In cell B2, type:

text=INT(A2)

Then drag the fill handle down.
Next, select the cells in column B, right‑click, choose Format Cells, and set the category to Date so they display as readable dates. This formula strips the decimal portion and keeps only the whole number that represents the date.

Step 2 – Extract the time
In cell C2, type:

text=A2-B2

Then drag this formula down.
Select column C, format it as Time (for example, h: mm or h: mm AM/PM), and Excel will show only the time portion. In practice, this method runs in under five seconds for tens of rows and updates automatically if the source data changes.

Method 2: Flash Fill (near‑instant, no formulas)

Flash Fill works especially well when you already see a clear pattern in your data.

Step 1 – Type the first date
Click B2, type the date part you see in A2 (for example, 01‑Jan‑2023), then press Enter.
Step 2 – Trigger Flash Fill
Go to B3, start typing the date from A3, then stop after a few characters and press Ctrl + E (or Data → Flash Fill on the ribbon). Excel detects the pattern and fills the entire column instantly.

Use the same process in column C for time: type one sample value, then press Ctrl + E again. Although Flash Fill is very fast, it does not update dynamically if the original cell changes, unlike the INT formulas.

Method 3: Power Query (fully automated, ideal for large datasets)

If you regularly import or refresh data, Power Query can split date and time once and then replay the split whenever new entries arrive.

Step 1 – Load into Power Query
Select your data range, go to Data → From Table/Range, and confirm in the Create Table dialogue.
Step 2 – Split by data type
In the Query Editor, select the datetime column, then choose Transform → Data Type → Date & Time (if needed). After that, click Transform → Split Column → By Delimiter, then choose Space as the delimiter. Power Query will instantly create separate Date and Time columns.

Finally, click Close & Load to push the cleaned two‑column output back into Excel. This method may take a few more clicks the first time, but it saves you from repeating the same steps every time you receive fresh logs or export files.

Practical table example (ready to copy)

The table below shows how your raw combined date‑time column looks before and after splitting, using the INT and subtraction method.

You can follow this video for your quick work

You can copy this structure into your own worksheet, paste dates in column A, then autofill the formulas in B2 and C2 down to the rest of the rows. Moreover, you can also format the date column as dd‑mmm‑yyyy and the time column as h: mm to match your regional preferences.

Transition-rich workflow comparison

To compare these methods, let’s walk through each step using many transition words.

First, choose your input data and decide whether you want dynamic formulas or one‑time cleanup. Next, if you prefer total control and automatic updates, then use the INT and subtraction technique. In contrast, if you want speed and do not mind static results, then Flash Fill is extremely convenient. Additionally, for recurring reports or large datasets, Power Query becomes the most efficient choice.

Before you start, ensure your datetime column is recognised as a date‑time value (check by changing the format to General; if it shows a decimal, Excel treats it correctly). Afterwards, if you apply any of these methods and the time column shows a date instead, then reformat that column as Time. Meanwhile, if you ever combine the two columns again later, you can add them back with =B2+C2 to reconstruct the original datetime.

Furthermore, if you already have multiple date‑time columns across the sheet, then repeat the same formulas or Flash Fill steps for each one. Similarly, if you export data from software that dumps timestamps into one field, then this splitting workflow will save you hours over the year. Finally, if you ever need to keep only the date for reporting, then just hide or delete the time column after splitting.

When to use which method

• Use INT+ subtraction when you want formulas that update automatically as the source data changes, and if you need precision for downstream calculations such as age or time‑between‑events.
• Use Flash Fill when you have a one‑off or small dataset and want to finish in literally 3–5 seconds, even if you avoid writing formulas.
• Use Power Query when you regularly refresh an external source (Google Sheets, CSV, database) and want Excel to reapply the split every time you refresh.

Each approach avoids manual typing, which drastically reduces errors and keeps your worksheet clean.

Call to action: subscribe for more Excel tips.

If you want more step‑by‑step guides on Excel automation, income‑tax‑ready templates, and time‑saving tricks, subscribe to pranab.banerjee83@gmail.com. Alternatively, you can subscribe to my website at www.pranabbanerjee.com for regular updates on Excel‑based tax tools, SEO‑friendly content frameworks, and other productivity workflows. Going forward, you will receive practical, plagiarism‑free tutorials designed specifically for tax professionals and content creators who rely heavily on Excel and automation.