Skip to main content

Combine date and time into one cell in Excel

Author: Xiaoyang Last Modified: 2020-03-03

Combining dates and times from two different columns into one single column as below screenshot shown, here will introduce some simple methods for dealing with task in Excel.

Combine separate date and time into one cell with TEXT function

Normally, you can apply the TEXT function in Excel to merge the two columns of date and time into one cell, the generic syntax is:

=TEXT (value, format_text)
  • value: The value or cell reference that you want to be converted to text.
  • format_text: The format you will apply for the cell value.

Please enter or copy the below formula into a blank cell where you want to get the result:

=TEXT(A2,"m/dd/yyyy ")&TEXT(B2,"hh:mm:ss")

Then, drag the fill handle down to the cells that you want to apply this formula, and all the dates and times have been merged into one cell. See screenshot:

Explanation of the formula:

TEXT(A2,"m/dd/yyyy "): This TEXT function is used to convert the cell value in cell A2 to a specific date format.

TEXT(B2,"hh:mm:ss"): This TEXT function will convert the cell value in cell B2 to a specific time format.

TEXT(A2,"m/dd/yyyy ")&TEXT(B2,"hh:mm:ss"): Using the & symbol to combine the two part formulas together to get the date and time formatting.

Note: With this TEXT function, you can also change the date and time format to your need, for more date formatting, please view here.

Combine separate date and time into one cell with a plus sign

In Excel, you can also use the plus sign to combine two cells which contain date and time, and then format the combined cell with the specific formatting you need.

1. Please apply the following formula into a blank cell:


2. And then, drag the fill handle down to the cells that you want to use this formula, and you will get the results as below screenshot shown:

3. Then, select the formula cells, and then right click, choose Format Cells from the right menu, see screenshot:

4. In the Format Cells dialog box, under the Number tab, click Custom from the left Category pane, and then type the date and time formatting: mm/dd/yyyy hh:mm:ss you like into the Type textbox, see screenshot:

5. Then click OK button, and all the formula cells have been converted to the date and time format you specified just now, see screenshot:

Relative functions used:

  • TEXT:
  • The TEXT function converts a value to text with a specified format in Excel.

More articles:

  • Combine Cells With Line Break
  • In Excel, sometimes, you may want to combine cells into one cell with line break as below screenshot shown. Here, in this tutorial, it introduces two formulas to solve this task with examples.
  • Move Or Combine Multiple Cell Contents Into One Cell
  • This tutorial will talk about how to combine multiple cell contents into one single cell as below screenshot shown. In Excel, you can use the & symbol, CONCATENATE or TEXTJOIN function to solve this task quickly and easily.
  • Extract Or Get Time Only From Datetime In Excel
  • If you have a list of datetime cells, now, you want to pull out all the times only from the datetime cells. In Excel, there are some functions can help you to solve this job, such as: TIME and MOD functions.
  • Get Or Calculate Age From Birth Date In Excel
  • If you have a list of the employee's date of birth in Excel worksheet, now, you want to calculate the age for each of them. In Excel, there are some useful functions, such as YEARFRAC or DATEDIF which can help you to get the age from the birthday quickly and easily.

The Best Office Productivity Tools

Kutools for Excel - Helps You To Stand Out From Crowd

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates  |  Delete Blank Rows  |  Combine Columns or Cells without Losing Data  |  Round without Formula ...
Super VLookup: Multiple Criteria  |  Multiple Value  |  Across Multi-Sheets  |  Fuzzy Lookup...
Adv. Drop-down List: Easy Drop Down List  |  Dependent Drop Down List  |  Multi-select Drop Down List...
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus  |  Design View  |  Big Formula Bar  |  Workbook & Sheet Manager | Resource Library (Auto Text)  |  Date Picker  |  Combine Worksheets  |  Encrypt/Decrypt Cells  |  Send Emails by List  |  Super Filter  |  Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters ...)  |  50+ Chart Types (Gantt Chart ...)  |  40+ Practical Formulas (Calculate age based on birthday ...)  |  19 Insertion Tools (Insert QR Code, Insert Picture from Path ...)  |  12 Conversion Tools (Numbers to Words, Currency Conversion ...)  |  7 Merge & Split Tools (Advanced Combine Rows, Split Excel Cells ...)  |  ... and more

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...


Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)

  • One second to switch between dozens of open documents!
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
Rate this post:
0   Characters
Suggested Locations