Aniruddah Alam
In many situations, we need to display the date and time simultaneously in one single cell in an excel worksheet. Maybe we have two columns containing the date and time separately, and we want to display them together. Or perhaps we want to display the current date and time combined in a cell. In this article, I will show you how to add date and time in excel and try to cover all the possible situations.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
4 Useful Methods to Add Date and Time in Excel
In this section, we will demonstrate 4 effective methods to add date and time in Excel with appropriate illustrations. To illustrate, we have taken a worksheet where in one column we have some dates and in another we have times. (see the figure below)
Now we want to add the date and time and combine them in a single cell. To do that, let’s use our first method.
1. Perform Arithmetic Operation to Add Date and Time
In this method, we will perform a simple arithmetic operation, which is adding date and time. Here follow the steps below.
Steps:
- First, choose a cell where we want to combine the date and time. Here, I have chosen cell D5.
- Now, write the following formula in the cell.
=B5+C5
- Now, press Enter key, and you will see that the Date and Time have been combined in the cell.
- Now, to perform the same task for the cells below, use Fill Handler to drag and copy the formula of D5.
- Consequently, you will see that all the dates and times have been combined and expressed under the Date & Time.
- This method is straightforward yet efficient and quick.
Read More: How to Add Time to Date in Excel (4 Useful Methods)
2. Combine CONCAT and TEXT Functions to Add Date and Time
In the 2nd method, we will use the CONCAT and TEXT functions to accomplish our task. To do that, follow the steps below.
Steps:
- Like the first method, select a cell where you want to add the date and time. In the figure below, I have selected the D5
- Now input the following formula and press Enter.
=CONCAT(TEXT(B5,"dd-mm-yyy")," ",TEXT(C5,"hh:mm:ss"))
- Here we can see that the date and time have been combined in cell D5. Now to apply the formula in the other cells, use the Fill Handler like in method 1. We will get the following result.
- As we can see, this method also yields the same result, but the time is in 24 hours format, which can be changed by changing the format.
Read More: How to Add Hours, Minutes, and Seconds in Excel
3. Use Shortcut Key to Add Date and Time
Sometimes we may need to input the current date and time combined in a cell. To do that, we can use 2 shortcut keys. To do that, follow the steps below.
Steps:
- Select a cell where you want to input the current date and time. Here we have selected cell C4.
- Now, press Ctrl + ; (Semicolon). As a result, the current date will be shown like the figure below.
- Now, press the Spacebar key and type Ctrl + Shift + ; (Semicolon). Consequently, the current time will be inserted like the figure below.
Read More: How to Add Time in Excel Automatically (5 Easy Ways)
Similar Readings
- How to Add Hours to Time in Excel (8 Quick Ways)
- Add 15 Minutes to Time in Excel (4 Easy Methods)
- How to Add Minutes and Seconds in Excel (3 Easy Methods)
- Add Milliseconds to Time in Excel (with Quick Steps)
- How to Add 30 Minutes to Time in Excel (3 Easy Methods)
4. Apply NOW Function to Add Date and Time
If you want to add the current time and date in your worksheet which will be dynamic, you can use the NOW function. To do that, follow the steps below.
Steps:
- First, choose a cell where you want to input the time and date. This time, we have chosen cell C4.
- Now input the following formula.
=NOW()
- Press You should see the following result.
- This Date and Time will update themselves when we reopen the worksheet or perform any operation on the worksheet.
- We can also change the format of the cell if necessary.
Read More: How to Add Time in Excel Over 24 Hours (4 ways)
Add Date and Time While Printing in Excel
While printing an Excel sheet, we may need to record and display the printing time and date on the sheet. To do this, follow the steps below.
Steps:
- First, go to the View tab and click on Page Layout
- As a result, you will see the worksheet in a page layout containing the Header and Footer sections. (See the figure below)
- You can add the date and time either in the Header or Footer. This time, I chose the Footer. The Footer has 3 parts for inserting anything: Left, Middle, and Right. Here I have chosen the Right part.
- To insert the date and time, go to Header & Footer tab and select Current Date.
- Now press the Spacebar key and again select Current Time.
- Now, if you go to print preview, you will see that the printing page has the date and time in the bottom right corner.
Things to Remember
- Use the first two methods for combining date and time, previously separated into two distinct columns.
- Use the 3rd and 4th methods for adding the current date and time.
Conclusion
That is the end of this article. If you find this article helpful, please share this with your friends. Moreover, do let us know if you have any further queries. Finally, please visit ExelDemy for more exciting articles on Excel.
