Once in your lifetime, you would have needed to combine date and time in Excel while working with different datasets. Suppose, your Excel sheet has a column containing dates and another column with times values.
And you want one more column in which both of these values needed to be combined. How would you do it? How to combine date and time in Excel? Generally, it is not something that you can do without effort. If somehow you manage to combine both columns in one separate column, it would be problematic when the formatting process starts. You could have faced issues in fixing these formatting issues. Fret no more, in this post you will find multiple ways that let you combine date and time in Excel.
This guide shows easy-to-follow methods:
- Combine Date and Time in Excel with Simple Addition Function
- Combine Date and Time in Excel with TEXT Function
- Combine Date and Time in Excel with Keyboard Shortcuts
How to Combine Date and Time in Excel with a Simple Addition Function?
As you know, Excel uses different languages to store different data types such as the date and time values in terms of numbers. For instance, Excel identifies 01-01-2021 09:00 AM as 44197.375.
In this number, you can see the integer value that shows a date and the decimal part shows a time value. By simply adding two columns, when one column has time and another column has a date, you can combine them easily. Adding two cells in one cell can combine them without difficulty.
Below is a dataset in which column C has the date and time.
For this, you can use the following formula:
=A2+B2
Put this formula in all cells and you will get the combined date and time values. You will see the format is being copied by default. The combined date and time in the cells you see in column C appear as decimal numbers in the back end.
With the help of the format cells dialog box, you can change the format of these cells.
Combine Date and Time in Excel with TEXT Function:
CONCATENATE function is used to combine two or more columns. On the other hand, the TEXT function lets you combine the content in the cells:
- First, you need to open the cell in which you have the date and identify the format you want.
- Now, refer to the time and identify the format you need.
- Use CONCAT to merge both columns with a space.
- Text function combine values
=TEXT(A2,”DD-MMM-YYYY”)&” “&TEXT(B2,”HH:MM:SS”)
Text function combines values2
While using CONCAT, the values in the arguments must be identified that needed to be combined. Here you can see three values that need to be combined.
- In the first text function, you have to get the date and apply a format.
- A space in between.
- In the second text function, you have to get the time value and apply a format.
Combine Date and Time in Excel with Keyboard Shortcuts:
Shortcut keys are real-time help when you have to perform a special function. Combining date and time with the help of keyboard shortcuts is a blessing. Below is the procedure:
- Choose cell C5 and press CTRL + ;
- It will add a date.
- Press CTRL + SHIFT + ;
It will add time to that cell. And it shows the current time.
- Choose cell C5 and navigate the cursor to the plus symbol.
- Drag it all the way to the end of the column and it will apply the same shortcut.
- Open the Home tab and choose the Number Format ribbon.
- You can change the format of the time and date with this procedure. Now, click on the drop-down menu and choose “More Number Formats”.
- Format Cells window will appear.
- Choose the “Custom” option and click on the desired format. Here “dd-mm-yy h: mm AM/PM format is selected.
- Press OK to save changes.
Wrap Up:
In this post, we have discussed multiple ways that answer how to combine date and time in Excel. Hopefully, this post helps you more than you needed. Practice can make your skills even better that’s why keep trying.
