While making a time log of activities in Excel, you may want to enter both the date and time in a single cell. In most cases, you may have dates in one column and time in another column in your worksheet. But Excel has some features and functions with which you can easily combine date and time values in a single cell. Today in this article we will discuss some methods to combine date and time in one cell in Excel.
Download Practice Workbook
Download this practice sheet to practice while you are reading this article.
Combine Date and Time in One Cell in Excel (4 Methods)
In this section, we will discuss four easy methods to combine date and time in a single cell in Excel.
1. Using Keyboard Shortcuts
We can easily combine date and time by using some simple keyboard shortcuts.
In the given situation, we have a dataset containing the names of some candidates. We have to enter their reporting date and time in the “Reporting Date and Time” column.
Now we will insert the date in cell C4 using the keyboard shortcut. Select cell C4 and press “CTRL+; (semicolon)” to enter a date. This will show today’s date.
To insert time, press “CTRL+SHIFT+; (semicolon)” in that cell. This will show your current time.
Select cell C4 and move your cursor to get the plus icon (+). Now drag it all the way to the bottom of the column to apply the same shortcut in all cells.
If you want to change the format of your date and time, then simply go to Home then in the Number Format ribbon, click on this drop-down icon to show the formatting options. Select “More Number Formats”
A new window appears called Format Cells. Here, select the “Custom” option and choose your suitable format for this column. We have chosen “dd-mm-yy h:mm AM/PM”. You can also modify the formats by adding criteria below the Type section.
Click Ok to change the format
2. Adding Date and Time in One Cell
In the following example, we have a dataset where the “Reporting Date” and “Reporting Time” of some candidates are given. We need to combine the values in those two columns into one column “Date and Time”.
In cell E4 of the Date and Time column, we will add the cell reference of the other two columns. So, the formula will be,
Where C4 is the cell reference of the “Reporting Date” column and D4 is the cell reference of the “Reporting Time” column. Put a Space before D4.
Press Enter to get the result.
We have our date and time in one cell. Now move your mouse cursor to the bottom right corner of the formula cell until it shows the Fill Handle sign (+). When it shows the sign, double click on it to apply the same formula to all the cells of the column.
If you want to change the format of the column, go to the Number Format ribbon and Select “More Number Formats”.
In the new window, select “Custom” and choose your suitable format for this column. We have chosen “dd-mm-yy h:mm AM/PM”.
Click Ok to continue. So, we have got our required format.
3. Using the TEXT Function
Let us show how easily you can combine date and time in one cell by using the TEXT function!
In cell E4, apply the TEXT function. Insert the values into the function and the final form is,
- Value is C4 and D4
- Format_text is “mmm/dd/yyyy “ and “hh:mm:ss”. You can apply different formats of date and time if you want.
Press Enter to apply the formula. We have combined our date and time in one single cell.
Now apply the same formula to the rest of the cells.
4. Using the CONCATENATE Function
The CONCATENATE function is one of the most essential Excel functions that allows you to connect several cell references into one cell in a worksheet.
In cell E4, apply the CONCATENATE with the TEXT function. Here we will use the TEXT function to define the text formats. Insert values into the formula and the final form is,
- Text1 is TEXT(C4,”dd-mm-yyyy”). We used the TEXT function to give the cell reference a definite format.
- Text2 is TEXT(D4,”hh:mm”)
- The space (“ ”) is given to separate the date and time values.
Press Enter to combine the values.
Now apply the same formula to get the final result.
Let’s say we want to change the time format from hh:mm to hh:mm AM/PM. To do that simply insert “AM/PM” into the TEXT function argument. And you will get the time in AM/PM format.
Also, you can add extra text like “Date:” or “Time:” just like given in the screenshot.
⏩ Using the Keyboard shortcuts will give you the current date and time.
⏩ You can choose and modify your date and time formatting from the Number Format option.
Combining the date and time in one cell is discussed in this article. We hope this article proves useful to you. If you have any confusion or suggestion regarding this article, you are always welcome to comment and share.