Excel is a powerful tool that can perform a wide range of functions, including date and time calculations. However, when you have separate date and time values in different cells, it can be cumbersome to work with them. Fortunately, Excel has a function called “concatenate” that allows you to combine date and time values into a single cell. In this article, we will explore how to concatenate date in Excel, step by step.

## Method 1: Using Concatenate Date in Excel Function

The Concatenate function is a powerful tool that allows you to combine text from multiple cells into one cell. In order to concatenate date and time in Excel, you can use the following formula:

=CONCATENATE(A1,” “,B1)

Here, A1 is the cell that contains the date, and B1 is the cell that contains the time. By adding a space between the two cell references within the formula, you can concatenate the date and time into one cell.

## Method 2: Using Ampersand Operator

Another way to concatenate date and time in Excel is by using the ampersand (&) operator. This operator works similarly to the Concatenate function and allows you to combine text from different cells into one cell. The formula for this method is as follows:

=A1&” “&B1

Here, A1 and B1 are the cells that contain the date and time, respectively. By adding a space between the two ampersands (&), you can concatenate the date and time into one cell.

## Method 3: Using Text Function

The Text function in Excel allows you to format data in different ways. In order to concatenate date and time using this function, you can use the following formula:

=TEXT(A1,”dd/mm/yyyy”)&” “&TEXT(B1,”hh:mm:ss”)

Here, A1 and B1 are the cells that contain the date and time, respectively. By specifying the date and time format within the Text function, you can concatenate them into one cell.

## Method 4: Using Custom Formatting

Excel also allows you to create custom formats for date and time. By creating a custom format that combines the date and time, you can easily concatenate them into one cell. The following steps will guide you on how to create a custom format:

1. Select the cell that contains the date and time.
2. Right-click on the cell and select Format Cells.
3. In the Format Cells dialog box, select Custom under the Category section.
4. In the Type field, enter the following custom format: dd/mm/yyyy hh:mm:ss
5. Click OK to save the custom format.

Once you have created the custom format, the date and time in the selected cell will be displayed in the format you specified. You can now easily concatenate the date and time into one cell.

## Tips for Concatenate Date in Excel

1. Make sure that the date and time cells are formatted correctly before concatenating them.
2. Use the “&” operator or the “concatenate” function to combine the date and time values into a single cell.
3. When using the “&” operator, separate the values with a space or any other character that you want to use as a separator.
4. Separate the values with a comma when using the “concatenate” function.
5. Always format the resulting cell to a valid date/time format before using it in any calculations.

## Conclusion

In conclusion, Excel’s concatenate function can be a handy tool when you want to combine separate date and time values into a single cell. By following the simple steps outlined in this article, you can easily concatenate date in Excel and use the resulting value in any date or time calculations. Always make sure to format the cells correctly and convert the resulting value into a valid date/time format before using it in any calculations.

### FAQs About Concatenate Date in Excel

1. #### Can I concatenate more than two cells in Excel?

Yes, you can concatenate as many cells as you want in Excel. Just separate the cells with a space or any other character that you want to use as a separator.

1. #### Can I concatenate cells with different date and time formats?

Yes, you can concatenate cells with different date and time formats. However, you need to make sure that the resulting value is formatted to a valid date/time format before using it in any calculations.

1. #### Can I concatenate cells with text and date/time values?

Yes, you can concatenate cells with text and date/time values. The resulting value will be a text value that includes the text and date/time values.

1. #### How can I separate the date and time values after concatenating them?

You can use the “Text to Columns” feature in Excel to separate the date and time values into separate cells.

1. #### Can I concatenate date and time values in reverse order?

Yes, you can concatenate date and time values in any order you want. Just make sure to separate them with a space or any other character that you want to use as a separator.

Tags: