This questioned was spawned by another question (dealing with the task described at the beginning of this one). The accepted answer to that one offers two types of solutions, one where the values from the second sheet are pulled into separate cells, and one where all of the values from the second sheet are concatenated into a single cell. Some of the cells on the second sheet contain date-times, which presents a problem for both approaches, and the fix is different for the two cases. I'll describe both.
What is happening
The stored date-time is being copied in its "raw", unformatted form. For example, Jan 6, 2015 8:55:08 AM is stored as 42010.3716 because it is the 42,010th day since 12/31/1899 (the standard reference date), and 8:55:08 AM is 37.16% of a day. The date-time is stored in the cell as a single number in that form and then Excel derives the year, month, day, and time information from it as needed to display the value in the desired format.
Values copied into separate cells
This is the simplest to fix. In this case, seeing the raw date-time is actually a good thing because it means that the date-time information has been preserved and can be used (you can use the value to do date-time testing, date-time arithmetic, etc.). You just need to format it.
One way is to copy the formatting from the original cell. Use copy, then in the new cell, paste-special format-only. You can access this by right-clicking the cells and using the context menues, or from the ribbon menu at the top.
The other way, particularly if you want to use a slightly different format for the copied value, is to use a custom format. For the new cell, access the cell format dialog by either right-clicking the cell or from the ribbon menu. Select Custom Format from the list of format types. You can either pick something close as a starting point from the list of special formats presented, or just enter the codes you want in the edit window.
The complete list of format codes can be found here. To duplicate the format described in the question, the code would be:
Yyyy/Mm/Dd Hh:mm:ss
The cell will then appear like the original.
Values concatenated into a single cell
In this case, the embedded value is already treated as text, so it has lost its value as a usable date-time. It is just a question of having it appear as a readable date-time in the string. This can be done by using the TEXT function to format the value while the string is being constructed. I'll borrow some code from the other question as the basis for this solution:
=IF($C2>0,INDIRECT("'tickets_info'!B" & $C2)
& " " & INDIRECT("'tickets_info'!C" & $C2)
...
& " " & INDIRECT("'tickets_info'!N" & $C2),"")
Here, C2 is a lookup value to find the matching row and the &'s are used to concatenate the various source cells into a single string containing all of the information for the matching ticket. Let's say column C in tickets_info is a date-time. When this string is created, that position in the string will show the raw date-time value instead of the formatted value displayed in tickets_info.
You can use the TEXT function to format that value. The TEXT function uses codes very similar to the custom format codes for the cell. The codes and a mini-tutorial can be found here. For the format described in the question, the result would look like this:
=IF($C2>0,INDIRECT("'tickets_info'!B" & $C2)
& " " & TEXT(INDIRECT("'tickets_info'!C" & $C2),"yyyy/mm/dd hh:mm:ss")
...
& " " & INDIRECT("'tickets_info'!N" & $C2),"")
The TEXT function is just applied to the derived cell reference (the result of the INDIRECT function).
