Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Combine Date and Time in One Cell in Excel (4 Methods)

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.

Using Keyboard Shortcuts to Combine date and time in one cell in Excel

  • To insert time, press “CTRL+SHIFT+; (semicolon)” in that cell. This will show your current time.

Using Keyboard Shortcuts to Combine date and time in one cell in Excel

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.

Using Keyboard Shortcuts to Combine date and time in one cell in Excel

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.

Using Keyboard Shortcuts to Combine date and time in one cell in Excel


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+ D5

Here, 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.

Utilizing Basic Sum Formula to Combine Date and Time

  • 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.

Utilizing Basic Sum Formula to Combine Date and Time

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.

Utilizing Basic Sum Formula to Combine Date and Time

  • 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.

Using TEXT Function to combine date and time in one cell

  • Press ENTER to apply the formula.
  • We have combined our date and time in one single cell.

Using TEXT Function to combine date and time in one 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.

Using CONCATENATE Function to combine date and time in one cell

  • Press ENTER to combine the values.

Using CONCATENATE Function to combine date and time in one cell

  • 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"))

Using CONCATENATE Function to combine date and time in one cell

  • 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"))

Using CONCATENATE Function to combine date and time in one cell

  • 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.


Further Readings

Asikul Himel
2 Comments
  1. No one mentions make then output cell permanent so the original Date and Time cells can be deleted.

  2. Reply Avatar photo
    Md. Abdur Rahim Rasel Sep 4, 2022 at 2:56 PM

    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.
    excel date and time in one cell
    Thank you for your question.
    Regards
    Md. Abdur Rahim Rasel (Exceldemy Team)

Leave a reply

ExcelDemy
Logo