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:
;
Using &:
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.
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.
Can someone show me how to combine a cell that has the date (9/12/2019) and one that has the time (09:19:15.234) into one cell that would read: "2019-9-12 09:19:15.234". Long story short i'm combining data from an old format (separate) to a new format that has it combined. The format the holds the cell of the combined date and time on the report i get is "general", not one of "time". Thank you.. Jamie
6-Aug-19 8-Aug-19
I want to combine these two dates in different cell to one cell like 6-Aug-19/8-Aug-19
You can use concatenation, explained here in detail.
https://www.exceltip.com/excel-text-editing-and-format/join-first-and-last-name-in-excel.html
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)
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.
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
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.
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.
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
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.
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
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
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