Combing date and time from two different cells into third cell

%3CLINGO-SUB%20id%3D%22lingo-sub-2389434%22%20slang%3D%22en-US%22%3ECombing%20date%20and%20time%20from%20two%20different%20cells%20into%20third%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2389434%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20combing%20a%20date%20from%20one%20column%20and%20a%20time%20from%20a%20second%20column%20into%20a%20third%20cell%20on%20another%20column.%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20tried%20to%26nbsp%3B%3CSTRONG%3ECombine%20data%20with%20the%20ampersand%20symbol%20AND%20Combine%20the%20data%20using%20the%20CONCAT%20Feature.%26nbsp%3B%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EInstead%20of%20the%20data%20in%20the%20two%20existing%20cells%20combining%20into%20a%20third%20cell%2C%20I%20get%20a%20weird%20number.%26nbsp%3B%3C%2FP%3E%3CP%3ECell%20A1%20%3D%205%2F21%2F2021%3C%2FP%3E%3CP%3ECell%20B1%20%3D%204%3A00pm%3C%2FP%3E%3CP%3EI%20want%20Cell%20C1%20to%20%3D%205%2F21%2F2021%204%3A00pm%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I've%20done%3A%3C%2FP%3E%3CP%3EClick%20on%20C1%2C%20%3DCONCAT(cell1%2Ccell2)%2C%20ENTER%26nbsp%3B%3C%2FP%3E%3CP%3Eand%26nbsp%3B%3C%2FP%3E%3CP%3EClick%20on%20Cell1%2C%26nbsp%3B%3CSTRONG%3E%3DA2%26amp%3B%22%20%22%26amp%3BB2%3C%2FSTRONG%3E%3CSPAN%3E.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20get%20both%20times%3A%26nbsp%3B%26nbsp%3B443380.625%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20I%20make%20it%20just%20take%20the%20data%20from%20both%20and%20dump%20it%20the%20same%20way%20into%20the%20third%20cell%20without%20some%20actual%20transformation%20of%20the%20data%20occurring%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2389434%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2389611%22%20slang%3D%22en-US%22%3ERe%3A%20Combing%20date%20and%20time%20from%20two%20different%20cells%20into%20third%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2389611%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1064175%22%20target%3D%22_blank%22%3E%40Lauren1980%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20format%20that%20cell%20using%20one%20of%20the%20custom%20formats%20you'll%20see%20the%20date%20and%20time%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_0-1622071163428.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F284157iB9201055704BF30E%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22mathetes_0-1622071163428.png%22%20alt%3D%22mathetes_0-1622071163428.png%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_1-1622071184919.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F284158i84B3C817740D63E7%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22mathetes_1-1622071184919.png%22%20alt%3D%22mathetes_1-1622071184919.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CTABLE%20width%3D%22304%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22304%22%3E12%2F6%2F13%203%3A00%20PM%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CTABLE%20width%3D%22304%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22304%22%3E443380.625%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESame%20cell%2C%20different%20formatting%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I am trying to combing a date from one column and a time from a second column into a third cell on another column. 

I've tried to Combine data with the ampersand symbol AND Combine the data using the CONCAT Feature.  

Instead of the data in the two existing cells combining into a third cell, I get a weird number. 

Cell A1 = 5/21/2021

Cell B1 = 4:00pm

I want Cell C1 to = 5/21/2021 4:00pm 

 

What I've done:

Click on C1, =CONCAT(cell1,cell2), ENTER 

and 

Click on Cell1, =A2&" "&B2.

 

What I get both times:  443380.625  

 

How can I make it just take the data from both and dump it the same way into the third cell without some actual transformation of the data occurring? 

 

2 Replies

@Lauren1980 

 

If you format that cell using one of the custom formats you'll see the date and time

mathetes_0-1622071163428.pngmathetes_1-1622071184919.png

12/6/13 3:00 PM
443380.625

 

Same cell, different formatting. In other words, your connecting them worked both times.

Hi,

This is most likely being caused by a formatting issue.
You can try converting both cells to TEXT when referencing them using the TEXT function.
=TEXT(A1,"dd/mm/yyyy")&" "&TEXT(B1,"h:mm am/pm")
=5/21/2021 4:00pm