How to Combine Date and Time in Excel: Best 6 Methods

In real-world scenarios, we often need to merge date and time in Excel when they are in separate cells.

Excel provides us multiple ways to concatenate date and time fields into one cell or format values with AM/PM.

In this guide, I will show you how to combine date and time in Excel using formulas, text functions, and formatting techniques.

By the end, you’ll know how to join dates and times and customize the output to suit your needs.

Let’s dive in.

Method 1: Combine Date and Time in Excel Using Addition

The easiest way to combine date and time is by adding the two values using a basic formula.

Suppose I have a date in cell A1 (e.g., “03/27/2025”) and a time in cell B1 (e.g., “2:30 PM”) and want to merge them.

Here’s how to do it:

Enter the Formula:

In a new cell (e.g., C1), type:

=A1 + B1

Press Enter:

When I press the Enter button, it should show me the combined date and time, right? But it is showing me only the date. Why? Because the format of the C1 cell is not correct.

Let’s correct the format of the C1 cell.

Format the Cell:

• Right-click on the cell
• Select “Format Cells,”
• Then choose “Custom.”
• And enter a format like:

mm/dd/yyyy hh:mm AM/PM

Now you will see the result as “03/27/2025 2:30 PM” in C1 cell.

Pro Tip: After merging, if your date or time isn’t displaying correctly, verify that source cells are formatted as “Date” and “Time” respectively.

Bonus Tip: If you ever need to do the reverse—separating date and time from a combined value—check out this guide on How to Separate Date and Time in Excel.

Method 2: Using the TEXT Function for Custom Formatting

If you don’t want to fall into formatting issues and need more control over how the combined result looks (e.g., with AM/PM or specific hour formats), you can use the TEXT function to concatenate the values.

Here’s how:

Formula: In cell C1, enter:

=TEXT(A1, "mm/dd/yyyy") & " " & TEXT(B1, "hh:mm AM/PM")

Result: This formula will merge the date and time into a text string, like “03/27/2025 2:30 PM.” In this case, you do not need to format the C1 cell.

Why Use This?

• This is an ideal way when you want a readable output without performing calculations.
• Also, you can tweak the format (e.g., “dd-mmm-yyyy h:mm” for “27-Mar-2025 14:30”).

Note: This method converts the result to text, so you can not use it for further date/time calculations unless you convert back to a number.

Related Tip: If you’re working with multiple columns and need to merge them efficiently, check out this guide on How to Combine Multiple Columns in Excel in VBA.

Method 3: Using CONCATENATE or CONCAT function

To merge date and time fields quickly, you can use the CONCATENATE function (Excel 2016 and earlier). In newer Excel versions, you can use the CONCAT function as well.

Let me show you an example:

CONCATENATE Formula:

=CONCATENATE(TEXT(A1, "mm/dd/yyyy"), " ", TEXT(B1, "hh:mm AM/PM"))

Or, using CONCAT:

=CONCAT(TEXT(A1, "mm/dd/yyyy"), " ", TEXT(B1, "hh:mm AM/PM"))

When to Use:

• You can use these functions for basic merging. But sometimes, you need to format cells if Excel doesn’t recognize the result as a date-time value.

Method 4: Combining Date and Time with Hour Precision

Sometimes, you only have a date and an hour value (e.g., “03/27/2025” in A1 cell and “14” in B1 cell for 2 PM). Is it possible to merge it?

Yes, here’s how to do:

Formula:

=A1 + (B1/24)

Here:

• A1 contains the date (e.g., “03/27/2025”).
• B1 contains the hour (e.g., “14”).
• Dividing by 24 converts the hour into a time fraction.

Format C1 cell: Use “mm/dd/yyyy hh:mm AM/PM” to display “03/27/2025 2:00 PM.”

Use Case: You can use it for schedules or logs where time is recorded in hours.

Method 5: Using TEXTJOIN Function (For Excel 2019 & Later)

If you want to merge multiple date and time fields, you can use the TEXTJOIN function.

Let’s see how to use it when you have 3/27/2025 in A1 cell and 10:30 AM in B1 cell.

TEXTJOIN formula:

=TEXTJOIN(" ", TRUE, TEXT(A1, "mm/dd/yyyy"), TEXT(B1, "hh:mm AM/PM"))

This formula will merge A1 and B1 values, and C1 will be populated with the merged date and time like: 3/27/2025 10:30 AM.

You can handle empty cells without error using this method. Also, it is efficient if you have a large dataset.

Method 6: VBA Macro to Automatically Merge Date and Time

For automation, you can use a VBA macro to combine date and time fields automatically.

Data before running macro:

Macro Code:

Sub MergeDateTime()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim lastRow As Long
    lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    Dim i As Integer
    
    For i = 2 To lastRow
        ws.Cells(i, 3).Value = ws.Cells(i, 1).Value + ws.Cells(i, 2).Value
        ws.Cells(i, 3).NumberFormat = "mm/dd/yyyy hh:mm AM/PM"
    Next i
End Sub

How to Use:

• Press Alt + F11 to open the VBA editor.
• Insert a new module and paste the code.
• Run the macro (Shortcut: Alt + F8) to merge date and time values.

Data after running macro:

Troubleshooting Common Issues

• VALUE! Error: Ensure your date and time cells are in the correct format (not text). Use VALUE() to convert if needed.
• Incorrect AM/PM: Double-check your time format in the source cell or formula.
• Blank Cells: Add an IF condition, like:

=IF(AND(A1<>"", B1<>""), A1 + B1, "")

This prevents errors when fields are empty.

Advanced Tip for Power Users

• Dynamic Timestamps: You can use =NOW() to insert the current date and time, then format it as needed.

Download the Free Practice Workbook

Ready to learn how to combine date and time in Excel? Download our free practice file to learn all six methods explained in this guide.

Try these methods on your dataset and let us know in the comments how it goes!

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.