Dates and times are stored as numbers in Excel... the date is the number of
days past "date zero" (which for Excel worksheets, but not VBA, was December
31, 1899), so the first date available in Excel (assuming you are not using
the 1904 system) is January 1, 1900 (offset one day from "date zero")...
your January 5, 2010 date is 40183 days offset from "date zero". This offset
number is what Excel stores for your date... when you tell Excel to format
the cell as a Date, it makes the display "human readable" as a date, but
Excel never stores the date as you see it. This date offset method of
storing a date is what give Excel the flexibility to display a date in
whatever format you want. As for time values, they are stored as fractional
numbers (the decimal part of a floating point number) and are simply the
fraction of a 24-day that the time represents; so, 3:00pm would be 15 hours
into the 24-day and Excel would store it as 15/24 which equals 0.625... that
is the number that Excel sees when you tell it a time value is 3:00pm. Your
10:55am time on your example data's first row would be seen by Excel as
(10+55/60)/24 which is 0.454861111 to nine decimal places. That means your
January 5, 2010 date at 10:55am would be stored as 40183+0.454861111 or, in
normal form, as 40183.454861111.
Okay, I went through all of the above so you can understand why
concatenation of date and time values will not work (well, there is a way to do
it with concatenations, but it is longer and less efficient than the proper
way)... they are numbers and, as the next to the last step in my first
paragraph shows, they just need to be added together. So, the formula you
should have in Column D (say second row for this example) is this...
and then format the cell as a date in whatever date format you want.
Rick (MVP - Excel)