Special date formatting merge with hours

[franck.so]

Hello,

I would need support for the following:
I have one column with a date 26.09.2014 and one with a time: 09:15
I would need to merge both cells to be displayed as per this format: 26/09/2014 09:15
I use this formula: =CONCATENATE(A1;" ";B2) and get this result: 41908 09:15

I've tried to change the date format in first cell but nothing works and I always get the ref. number of the date instead.
Is somebody would have a solution?

Thanks in advance

Franck

[ChemistB]

Simply =A1+B2 and format the cell with date and time.

[ChemistB]

At a second look, the fact that when you concatenate, it comes out 41908 09:15 indicates that your time is stored as text, not time (check by =ISNUMBER(B2) should be TRUE)
That may need to be fixed first. How are all your times presented?

[franck.so]

Thanks a lot for your answer,
That was simple actually.
To reply to your question; my date is formatted as a date. However my time (hh:mm) is formatted as a text. do you know how I could change "9:15 text formatted" to "9:15 time formatted"?

thanks

Franck

[ChemistB]

If Excel recognizes it as a time and there are no extra spaces or anything to throw it off, the simple act of adding it to the date will convert the result to a number. (If not, you'll get an error).

If you need the cells with time to be numerical for other operations, try doing a Text to Column (on Data tab in 2007 and up).