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

final result

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.

Step-1:

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.

Creating table For keyboard shortcut

Step-2:

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.

Keyboard shortcuts to combine date and time

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

Keyboard shortcuts to add time

Step-3:

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.

Keyboard shortcut to combine date and time

Step-4:

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 drop-down  to show the formatting options. Select “More Number Formats”

Changing formats for date and time

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.

New formats for date and time

Click Ok to change the format

Combination of date and time

2. Adding Date and Time in One Cell

Step-1:

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

creating table for Adding date and time

Step-2:

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,

=C4+ D4

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.

adding columns for date and time

Press Enter to get the result.

Result by adding date and time

Step-3:

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.

Result for adding date and time

Step-4:

If you want to change the format of the column, go to the Number Format ribbon and Select “More Number Formats”.

changing formats of date and time

In the new window, select “Custom” and choose your suitable format for this column. We have chosen “dd-mm-yy h:mm AM/PM”.

getting the format for date and time

Click Ok to continue. So, we have got our required format.

final result of adding date and time

3. Using the TEXT Function

Let us show how easily you can combine date and time in one cell by using the TEXT function!

Step-1:

In cell E4, apply the TEXT function. Insert the values into the function and the final form is,

=TEXT(C4,”mmm/dd/yyyy “)&TEXT(D4, “hh:mm:ss”)

Where,

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

applying the TEXT formula for date and time

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

getting result for the TEXT formula

Step-2:

Now apply the same formula to the rest of the cells.

final result using the TEXT formula

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.

Step-1:

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,

=CONCATENATE(TEXT(C4,”dd-mm-yyyy”),” “,TEXT(D4,”hh:mm”))

Where,

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

entering formula to combine date and time

Press Enter to combine the values.

getting result for the date and time

Step-2:

Now apply the same formula to get the final result.

final result using the CONCATENATE function

Step-3:

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(C4,”d-mm-yyyy”),” “,TEXT(D4,”hh:mm AM/PM”))

getting result

Also, you can add extra text like “Date:” or “Time:” just like given in the screenshot.

=CONCATENATE(“Date: “,TEXT(C4,”d-mm-yyyy”),” “,”Time: “,TEXT(D4,”hh:mm AM/PM”))

formatting for date and time

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

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo