How to Separate Date and Time in Excel: 6 Best Mathods

Ever Wondered How to Separate Date and Time in Excel?

Excel is a powerful tool for managing data, but sometimes dates and times come combined in a single cell, making it tricky to work with them separately.

Whether you’re analyzing timestamps or organizing schedules, splitting date and time into separate columns can simplify your workflow.

In this article, I will explore multiple methods—using formulas, with Text to Columns, Power Query, VBA Macro, Flash Fill, and more—to help you separate date and time effortlessly in any sheet.

Why Separate Date and Time?

When a timestamp like “03/26/2025 14:30” sits in one cell, it’s stored as a single value in Excel. However, splitting it into a date (03/26/2025) and a time (14:30) in separate columns allows for better sorting, filtering, and calculations.

Let’s dive into the methods.

Method 1: Separate Date and Time Using Formulas

This is one of the easiest and most flexible ways to split date and time from one cell. Excel has a built-in function, INT, that can extract these components.

Steps:

Step 1: Identify Your Data:

Assume your timestamp is in cell A1 (e.g., “01/15/2025 14:30”).

Example Data Before separating date and time:

Step 2: Extract Date From One Cell

• In a new cell (e.g., B1), enter this formula:

=INT(A1)

• This removes the time portion and leaves the date. Format B1 cell as a date (e.g., “mm/dd/yyyy”).

Step 3: Extract Time From One Cell

• In another cell (e.g., C1), Use this formula:

=A1-INT(A1)

• This isolates the time. Format C1 cell as a time (e.g., “hh:mm”).

Step 4: Apply the same formula in the remaining cells

• Copy Down: Drag both formulas down the column to apply them to more rows.

Data after separating date and time:

How It Works:

Excel stores dates and times as numbers. It stores dates as a whole number and time as a decimal in the A1 cell. The INT function will chop off the decimal (time) and leave the date in the B1 cell. While subtracting the integer isolates the time in the C1 cell.

Dragging B1 and C1 cell formulas will apply the same formula to the remaining cells.

Method 2: Using Text to Columns

If your timestamp has a space between the date and time (e.g., “03/26/2025 14:30”), Excel’s Text to Columns feature is a quick shortcut—no formulas needed!

The Text to Columns feature not only helps in separating date and time but also works great for splitting text. Check out this guide on how to Split Text in Excel for more details.

Steps:

Steps 1: Select the Column with Date-Time Values

• Select & highlight the column with your timestamps (e.g., A1:A6).

Step 2: Open Text to Columns Wizard

• Go to the Data tab > Text to Columns (or use the shortcut: Alt + A + E) to open the convert text to columns wizard window.

Step 3: Choose the Separation Method

• Select “Delimited” and click Next.

Step 4: Set the Delimiter

Choose Space as the delimiter (if date and time are separated by a space) and click Next.

Step 5: Set destination to split date and time

• Choose where to place the split data (e.g., =$B$1:$C$6) and click Finish.

Step 6: Verify the separated date and time

When you click on the finish button, the separated date and time will be populated in C and D columns, respectively. Verify if there is any error after date and time separation.

After splitting, format the new columns as “Date” and “Time” to ensure proper display.

Quick Tip: This method works best if the date and time are consistently formatted with a space.

Method 3: Separate Date and Time With a Single Formula (TEXT())

For a more advanced approach, you can use specific functions like TEXT to split date and time with a formula in one go.

Steps:

Suppose you have a date and time in the A1 cell like 07/19/2025 22:30:15

Now, let us see step-by-step how to extract the date and time and populate it in separate columns.

Step 1: Separate Date

• In cell B1, enter:

=TEXT(A1,"mm/dd/yyyy")

• This pulls just the date (i.e., 07/19/2025) as text.

Step 2: Extract Time

• Enter the following formula in cell C1:

=TEXT(A1,"hh:mm")

• This isolates the time (i.e., 22:30:15) as text.

Step 3: Convert to Values (Optional):

• If you need proper date/time values (not text), copy the results, paste as values, and reformat the cells.

Note: This method is great for display purposes but may require extra steps for calculations.

Method 4: Separate Date and Time Using VBA Function

VBA Macro is a powerful option if you need to automate the separation process for a large dataset.

Steps:

Example Data before split:

Step 1: Open the VBA Editor

• Press Alt + F11 to open the VBA editor.
• Click Insert → Module.

Step 2: Add the VBA Code

Sub SplitDateTime()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row 'Find last row
    
    For i = 2 To lastRow 'Assuming data starts from row 2
        ws.Cells(i, 2).Value = Int(ws.Cells(i, 1).Value) 'Extract Date
        ws.Cells(i, 3).Value = ws.Cells(i, 1).Value - Int(ws.Cells(i, 1).Value) 'Extract Time
    Next i
    
    ws.Columns(2).NumberFormat = "mm/dd/yyyy"
    ws.Columns(3).NumberFormat = "hh:mm:ss AM/PM"
End Sub

Step 3: Run the VBA Macro

• Select the column with date-time values.
• Run the SplitDateTime macro (Alt + F8 → Select SplitDateTime → Run).
• The date will appear in column B, and the time in column C.

Date Result After Running VBA:

Note: This VBA function works dynamically and can be applied to large datasets.

We can use a VBA Macro to Combine Two Columns and find and replace text as well.

Method 5: Using Power Query (Best for Large Datasets)

Power Query provides a dynamic way to separate date and time automatically.

Steps:

Step 1: Select Data Column

• Click on the column that contains date-time values.
• Go to Data → Get & Transform → From Table/Range.
• Create table prompt will open. Ensure your data has headers and click OK.

Step 2: Open Power Query Editor

• The Power Query Editor window will open with your selected data.

Step 3: Isolate the Date

• Click on the Date-Time column.
• Go to Add Column → Date → Date Only.

• A new column will be created with just the date.

Step 4: Extract the Time

• Click on the Date-Time column again.
• Go to Add Column → Time → Time Only.

• This will create another column with only the time.

Step 5: Close & Load the Data

• Click Close & Load to apply the changes.
• The data will now appear in Excel with separate date and time columns.

The extracted date and time will be populated as below.

Tip: Power Query is ideal for large datasets as it updates dynamically when new data is added.

Method 6: Using Flash Fill (Quickest Method) – Step-by-Step

Steps:

Step 1: Manually Enter the First Date

• In column B (or another column), type the date from the first row manually (e.g., 5/12/2025).

Step 2: Manually Enter the First Time

• In column C, type the time from the same row (e.g., 8:20:10 AM).

Step 3: Use Flash Fill for the Date Column

• Click on the next cell in column B (below the first manually entered date).
• Press Ctrl + E (Flash Fill shortcut).

• Excel will automatically fill in the rest of the dates.

Step 4: Use Flash Fill for the Time Column

• Click on the next cell in column C.
• Press Ctrl + E again.

• Excel will extract and fill the time values for all rows.

Quick Tip: If Flash Fill doesn’t work, ensure that the original data format is consistent and recognized by Excel.

Bonus Tips

• Check Your Data: If your timestamps lack a space, Text to Columns won’t work—stick to formulas.
• Combine Methods: Use Text to Columns to split initially, then refine with formulas for specific formatting.
• Formatting Matters: Always adjust the cell format (Right-click > Format Cells) after splitting to match your needs.

Which Method Should You Use?

• Using a formula: Best for dynamic updates when your data changes.
• With Text to Columns: Ideal for quick, one-time splits from one cell.
• Shortcut lovers: Text to Columns is faster if you’re comfortable with delimiters.
• Using Power Query (best for large datasets and automation)
• Using Flash Fill (best for quick, small datasets)
• Using a VBA function (best for automation and large datasets)

By mastering these techniques, you’ll have full control over your timestamps in Excel. Try them out on your next sheet and see which works best for your data!

Choose the method that fits your workflow and improve your Excel sheet management.

Ready to learn how to separate date and time in Excel? Download this Excel practice file with pre-filled data and test each technique step by step!

Do you have any other Excel tips you’d like to learn? Let us know in the comments!

Aravind

Hi, I’m Aravind, a seasoned Automation Test Engineer with 17+ years of industry experience. I specialize in tools like Selenium, JMeter, Appium, and Excel automation. Through this blog, I share practical tutorials, tips, and real-world insights to help testers and developers sharpen their automation skills.