If your Excel sheet has a column with combined dates and times, you might want to separate date and time into different columns. My short video shows the steps, and there are written steps below.
Step 1: Prepare Your Data
Before you begin to split date and time in Excel, make sure your data is in a single column with dates and times combined.
For example, you might have something like column A, in my screenshot below:
I want dates from the combined date/time (column A), to show up in column B.
Later, I’ll add the times to column C.
Note: The format of your dates/times is important. Make sure they’re in a consistent format before you move on to step 2.
Step 2: Use Flash Fill to Separate Dates
To split your dates quickly, you can use Excel’s Flash Fill feature. This feature automatically adds data to the worksheet, based on your input. Hereβs how:
- Type the first date from your column in an adjacent column. I started in cell B2.
- Directly below that, type the second date.
- Next, select the cell below the 2nd date
- Finally, press the Flash Fill shortcut — Ctrl + E
Excel should automatically fill down the rest of the dates for you.
Flash Fill makes the job quick and easy and saves you lots of time – especially in a long list!
Step 3: Use Flash Fill to Separate Times
After the dates are done, you can do the same steps, for the times. Follow these steps:
- Type the first time from your original column in another adjacent column. I started the times in cell C2
- Then, type the second time right below it.
- Next, select the cell below the 2nd time, and press Ctrl + E again.
This will fill down the rest of the times for you just like it did with the dates!
Step 4: Check Your Results
Warning: After you use Flash Fill, be sure to check (and double-check) your results.
Make sure all of the dates and times are accurate. In some of my worksheets, I was surprised to see that Flash Fill didn’t work correctly.
In a long worksheet, it might be easy to overlook the problem, if you just check the first few rows.
For example, when I had dates formatted as month/day/two-digit year, Flash Fill didn’t work correctly in some rows.
Dates Are Text, Not Real Dates
You can see that problem in my video, starting at the 1:06 minute mark, and in the screenshot below.
In column B, some of the “dates” are left aligned, and that usually indicated a cell that contains text — not a real date or number.
Important Note About Formats
If you notice that Flash Fill gave some odd results with dates, I recommend using a format that has a 4-digit year.
At least that solved the problem for me!
- I changed the combined date/time column to a date format with a 4-digit year
- Then I tried the Flash Fill feature again
- All the dates were perfect — no problems with “text” dates.
More Examples
Flash Fill is quick and efficient, at least when it works correctly!
For more ways to split dates and times in an Excel worksheet, go to the Split Date and Time in Excel page on my Contextures site.
You can get my sample file there too – the one that I used in the video.
__________________
How to Split Date and Time in Excel
__________________
Original source: https://contexturesblog.com/archives/2024/12/31/excel-split-date-time/?utm_source=rss&utm_medium=rss&utm_campaign=excel-split-date-time