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
  1. Combining Data From Seprate Coloum into Single Data and time

;

 

 

 

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 Data From Seprate Coloum into Single Data and time1

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

Combining Data From Seprate Coloum into Single Data and time2

Formula 2:    =a1+b1

Combining Data From Seprate Coloum into Single Data and time3

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!
      Site Admin

  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?

    Thanks in advance.

  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?

    Thanks in advance.

    • 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)
    how can i get future date using formula. Please help!!!

    • hi, phyo wai myint. if your date is in cell a1 than you can write this formula to get a future date.
      =DATE(YEAR(A1)+3,MONTH(A1)+8,DAY(A1)+21)

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube