Combine date and time into one cell in Excel
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
- Combine separate date and time into one cell with a plus sign
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:
- 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:
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 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.
