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




-- 
Linda

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

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"mathel" <mathel@discussions.microsoft.com> wrote in message 
news:357C5DEE-04EC-4468-A1F9-8CB0C34AE628@microsoft.com...
> 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
>
>
>
>
> -- 
> Linda 

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


"mathel" <mathel@discussions.microsoft.com> wrote in message 
news:357C5DEE-04EC-4468-A1F9-8CB0C34AE628@microsoft.com...
> 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
>
>
>
>
> -- 
> Linda 

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.

-- 
Rick (MVP - Excel)


"mathel" <mathel@discussions.microsoft.com> wrote in message 
news:357C5DEE-04EC-4468-A1F9-8CB0C34AE628@microsoft.com...
> 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
>
>
>
>
> -- 
> Linda