Concatenate 2 columns date & time

[mathel]

Hi, I am working with Excel 2003 and have a ws that I need to combine column
B which is a date, with column C which contains the time. No matter how I
have formatted column D, I cannot get the date to display correctly.

What I have is: B C
05/01/2010 10:55
24/12/2009 09:35

I need column D to show as: 05/01/2010 10:55
24/12/2009 09:35

I have tried several different formulas/formatting in Column D but always
end up with the result in Column D as: 40183 10:55
40171 09:35

I am hoping someone can help solve my problem.

Many Thanks

 

[Don Guillett]

You need to format each one
=text(a1,????) & " " & text(b1,?????????)

 

[Bernard Liengme]

If the data in B is a real date (not text) and in C a real time (not text)
then you do not need to concatenate but simple add: =B1+C1
For how Excel stores date and time see
http://www.cpearson.com/excel/datetime.htm

Failing that, try =TEXT(B1,"dd/mm/yyyy")&" "&TEXT(C1,"hh:mm")
best wishes

 

[Rick Rothstein]

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 won't 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...

=B2+C2

and then format the cell as a date in whatever date format you want.