How to Combine Date and Time in Excel [3 Methods]

To combine date and time, follow the steps below:

1. Select a blank cell.
2. Type the formula: =TEXT(B2,”dd-mm-yyyy”) & TEXT(C2,”   hh:mm AM/PM”)
3. Use the Fill Handle to copy the formula down.

This formula concatenates date values from cell B2 and time values from cell C2 into a single text string.

Method 1: Combine the Date and Time with the Arithmetic Operator

If you want to combine date and time in Excel, you can use the plus (+) sign between the cells that contain date and time. Follow the steps below:

1. Copy the formula to add the value of cells B2 and C2: =B2+C2.
   
   If your cell is not in date format, you need to format it. To format the cells, click CTRL+1. Then, you will see the Format Cells dialog box. You can customize it from this tool.
   
   
2. Use the Fill Handle to drag the formula down to the column.
   
   Here is the combined date and time using the plus sign.
   

Method 2: Combine the Date and Time in Excel with the Application of the TEXT  Function

To combine date and time, using the TEXT function is an alternative way.

Syntax

=TEXT(value, format_text)&TEXT(value, format_text)

Formula

=TEXT(B2,"dd-mm-yyyy") & TEXT(C2,"   hh:mm AM/PM")

Formula Explanation

The formula takes a date from cell B2, formats it as “dd-mm-yyyy”, takes a time from cell C2, formats it as “hh:mm AM/PM”, and then concatenates the two formatted values. The result is a text string representing both the formatted date and time.

To combine date and time, using the TEXT function:

1. Copy the formula: =TEXT(B2,”dd-mm-yyyy”) & TEXT(C2,”   hh:mm AM/PM”)
2. Paste it into cell D2.
3. To drag the formula to the column, use Fill Handle.
   

This is the output after using the TEXT function to combine date and time.

Method 3: Combine Date and Time in Excel by Applying the CONCATENATE Function

The CONCATENATE function combines multiple text strings into one single text string. It allows you to join text from different cells or add specific text elements together.

Syntax

=CONCATENATE(TEXT(value, format_text), “ ”, TEXT(value, format_text))

Formula

=CONCATENATE(TEXT(B2,"dd-mm-yyyy"), " ", TEXT(C2,"hh:mm AM/PM"))

Formula Explanation

The formula takes a date from cell B2, formats it as “dd-mm-yyyy”, takes a time from cell C2, formats it as “hh:mm AM/PM”, and then concatenates the two formatted values with a space in between.

To combine Date and Time in Excel by Applying the CONCATENATE Function:

1. Select a cell.
2. Copy the formula: =CONCATENATE(TEXT(B2,”dd-mm-yyyy”), ” “, TEXT(C2,”hh:mm AM/PM”))
3. Paste it into the selected cell.
   
4. Drag the formula down to the column by using the Fill Handle.
   

This is the output after using the CONCATENATE function to combine date and time.

Shortcut Keys to Format Cells that Contain Data and Time

You can use some shortcuts to format the cells for date and time.

• To enter a date in a cell, press CTRL+; (semicolon)
• To insert the current time, press CTRL+SHIFT+; (semicolon)

Conclusion

Excel offers various functions and formulas to combine date and time in one cell. You can track data information of time-sensitive information, and organize the data more efficiently by merging the date and time. In this article, I have included 3 different methods to combine date and time in Excel. Additionally, I have added some shortcut keys to format cells. Hence, I hope you can resolve the issues about combining the date and time along with formatting by yourself now.

Frequently Asked Questions

How do I concatenate dates in Excel?

To concatenate dates in Excel, use either the CONCATENATE function or the “&” operator. For example, =CONCATENATE(A1, ” “, B1) or, =A1 & ” ” & B1, where cells A1 and B1 are the cell references containing the date values.

How do I create a date and time series in Excel?

To create a date and time series in Excel, enter the start date and time, then drag down to fill cells. For specific time intervals, use the formula: =A1 + TIME(0,15,0), adding 15 minutes. Format cells for the preferred date and time display.

How do you add time in Excel?

To add time in Excel, enter time values in separate cells and use the SUM function (e.g., =SUM(A1, B1)). Format the result cell to display the sum as time.

How do I add 30 days to a date in Excel?

To add 30 days to a date in Excel, follow these steps:

1. In a cell, enter the date you want to add days to.
2. In a different cell, use the formula =A1+30, where cell A1 is the cell containing the original date.
3. Press ENTER to get the result, which will be the original date plus 30 days.
4. Format the cell containing the result to display the date in the desired format.

Explore these simple steps to effortlessly add 30 days to any date in Excel, boosting your efficiency in date calculations.