# Combining Data from Separate Columns into a Single Date and Time Value

While working in Microsoft Excel, it is very common that we have to work on combining data from multiple columns in a worksheet in Microsoft Excel 2010.

Let’s take an example of Column A and Column B

In our excel worksheet, Column A contains First Name and Column B contains Last Name. We can either use Concatenate Formula or we can use “&”

Concatenate:

•  Column A contains First Name
•  Column B contains Last Name
•  Syntax is =CONCATENATE(text1,text2,…)
•  Formula is =CONCATENATE(A2,B2)
•  Select Cell C2
•  Write =CONCATENATE(
•  Select Cell A2
•  Select Cell B2
•  Press Enter on your keyboard
• A2 has John
• B2 has Smith
• C3 will show the result as JohnSmith

;

Using &:

• Column A contains First Name
• Column B contains Last Name
• Syntax is =Cell 1 & Cell2
• Select Cell C2 with the mouse
• Write =
• Select Cell A2
• Write “&”
• Select Cell B2
• Press Enter on your keyboard.
• A2 has John
• B2 has Smith
• C3 will show result the as JohnSmith

Combining Date and Time Value from separate Columns into a single Cell

We have seen how to combine data from 2 columns. Similarly, let’s see how to combine date and time which are present in 2 different columns into one column.

Let’s take an example of Column A and Column B.

Column A contains Date and Column B contains Time.

Column A contains 11/23/2014 and Column B contains 2:30:54 PM.

We want the answer as "11/23/20142:30:54 PM"

There are 2 ways to get this result:

Formula 1:    =TEXT(A1,"dd/mm/yyy")&" "&TEXT(B1,"hh:mm:ss")

Formula 2:    =a1+b1

Problem 2:

Columns A:F contain values representing month, day, year, hours, minutes and seconds.
We want to combine the numbers from each row into a single date and time value.

Solution:

Use the DATE and TIME functions as shown in the following formula:
=DATE(C2,A2,B2)+TIME(D2,E2,F2)
Format the results (column G) as "mm/dd/yy hh:mm:ss".

In this way we can join date & time from two cells into one cell.

## Users are saying about us...

1. problem

I have two data coloumns in excel sheet which contains date and time. One coloumn contains even hours of date and other coloumn contains odd hours of date. I need to make a third coloumn to include both odd and even hours of date.

• I am supposing that you have date or date with hour and second in column A and in column B you have time
in HH:MM format,

Just put the formula =IF(LEN(A3)<=6,A3+TEXT(B3,”hh:mm”),TEXT(A3,”mm/dd/yyyy”)+TEXT(B3,”hh:mm”)) also pls change the date format as custom then m/d/yyyy h:mm pls let me know in case of any further assistance Rishi saw

2. I'm using the formula =DATE(C2,A2,B2)to combine 3 columns that include numbers for month, day, year, but the resulting value, while in the correct format, doesn't reflect the date that I want. So, 03 11 2014 converts to 5/6/1909. I've tried reformatting the cells, but am having no luck in rendering the correct result. Any suggestions?

• Hi Elizabeth,

MS Excel takes the date command in the form of =DATE(YEAR,MONTH, DATE). You have to change the format of the date obtained in this manner. I think you might have referred it to a "4 digit" month, so it might be throwing this error.

Regards,
Vamsi

3. date date
01-10-15 01-10-15
02-10-15 05-10-15
06-10-15 07-10-15
08-10-15 09-10-15
10-10-15 10-10-15
how to transfor it in single columns by ordring ?? plz

4. HI,

I got a Two colums 2/19/16 6:00 AM (column A) and 2/19/16 9:00 AM (column B) need to combine both to column c, can u pls help on this .....
expected result should be either 2/19/2016 6:00 AM to 2/19/2016 9:00 AM (with the word TO between two formats)

OR

2/19/2016 6:00 AM 2/19/2016 9:00 AM (just a space between two formats

anything is fine , pls help me on this

• Hi Jebaselvan,

If cell A1 has value "2/19/2016 6:00:00 AM" and cell B1 has value "2/19/2016 9:00:00 AM" then you can use the following formula in cell C1 to combine the time.

```=TEXT(A1,"d/mm/yyyy h:mm AM/PM")&" to "&TEXT(B1,"d/mm/yyyy h:mm AM/PM")
```

In the above formula, you can customize and instead of "to" you can have space to meet the 2nd expected output.

Happy Learning!

• Will this work if I am combining two rows? Example: I am looking at time punches and auditing to see if the employee has worked more than 10 hours in a day, however, the report gives me time punches rather than hours worked for that particular day.

5. I am trying to combine a column that contains DD/MM/YYYY HH:MM:SS to another column that contain milliseconds 950 into one column. Can you please advise on how I can do this?

6. I am trying to combine a column that contains DD/MM/YYYY HH:MM:SS and another column that contains milliseconds for example 950 into one column. Can you please advise on how I can do this?

• so if the column A2 has 12/04/2016 12:39:06 and the other column has 950, I would like to combine them to become 12/04/2016 12:39:06.950

7. If Column A has the day and Column B has the month (Column A: Numeric from 1 to 31, Column B: Abbreviation for month, for example Jun / Jul) - can that be combined and have a result in Date Format?

• Yes, it can be done. Combine the number date and abbreviation of the month with -, and encapsulate it with the VALUE function.
for example, if you have 1 in A1, Jan in B1 then this will formula will give you a serial number of 1-Jan-2019.
=VALUE(A1&"-"&B1)

change the cell format to a date format.

8. phyo wai myint

How to combine 2 date in one cell for future date. (Eg; 15/2/2014 and 3 Years, 8 Months and 21 Days)