Asikul Himel
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.
4 Methods to Combine Date and Time in One Cell in Excel
In this section, we will discuss 4 easy methods to combine date and time in a single cell in Excel.
Suppose we have a dataset of some Employee Names. Now we will add their reporting Time and Date in one cell using some simple tricks.
1. Using Keyboard Shortcuts to Combine Date and Time
We can easily combine date and time by using some simple keyboard shortcuts.
Step 1:
- Select cell (C5) and press “CTRL+; (semicolon)” to enter a date.
- To insert time, press “CTRL+SHIFT+; (semicolon)” in that cell. This will show your current time.
Step 2:
- Select a cell (C5) 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.
Step 3:
- 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. Utilizing Basic Sum Formula to Combine Date and Time
In the following example, we have a dataset where the “Reporting Date” and “Reporting Time” of some employees are given. We need to combine the values in those two columns into one column “Date and Time”.
Step 1:
- In cell (E5) of the Date and Time column, we will add the cell reference of the other two columns. So, the formula will be-
=C5+ D5Here, Cell (C5) is the cell reference of the “Reporting Date” column and D5 is the cell reference of the “Reporting Time” column. Put a Space before D5.
- Press ENTER to get the result.
Step 2:
- 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 column cells.
Step 3:
- 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. Combine Date and Time in One Cell Using TEXT Function
Let us show how easily you can combine date and time in one cell by using the TEXT function!
Steps:
- In cell (E5), apply the TEXT function. Insert the values into the function and the final form is-
=TEXT(C5,"mmm/dd/yyyy ")&TEXT(D5, "hh:mm:ss")Where,
- Cell 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, pull the “Fill Handle” down to fill all the cells.
- Finally, we have our combined date and time in one cell.
4. Using CONCATENATE Function
The CONCATENATE function is one of the essential Excel functions that allows you to connect several cell references into one cell in a worksheet. Follow the instructions below-
Step 1:
- In cell (E5) 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-
=CONCATENATE(TEXT(C5,"dd-mm-yyyy")," ",TEXT(D5,"hh:mm"))Where,
- Text1 is TEXT(C5,”dd-mm-yyyy”). We used the TEXT function to give the cell reference a definite format.
- Text2 is TEXT(D5,”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.
Step 2:
- 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.
=CONCATENATE(TEXT(C5,"d-mm-yyyy")," ",TEXT(D5,"hh:mm AM/PM"))
- Thereafter, click ENTER and then pull the “Fill Handle” down to fill.
- Also, you can add extra text like “Date:” or “Time:” just like given in the screenshot.
=CONCATENATE("Date: ",TEXT(C5,"d-mm-yyyy")," ","Time: ",TEXT(D5,"hh:mm AM/PM"))
- Simply, press ENTER and drag down the “Fill Handle”.
Quick Notes
⏩ 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.
Conclusion
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.
No one mentions make then output cell permanent so the original Date and Time cells can be deleted.
Hi TERRY MUNDY!
You can easily make the output cell permanent through you will delete the original Date and Time cells. First of all, use Ctrl + C on your keyboard to copy the output cell. Hence, use the paste special features to paste the value only from your Home ribbon. After that, you can delete the original Date and Time cells, the output cell will become permanent. Look at the below screenshot.
Thank you for your question.
Regards
Md. Abdur Rahim Rasel (Exceldemy Team)