Combining cells for a time value in excel 2000

[westma]

I have what looks like this:

Time Date
230 11902
240 11902
250 11902
300 11902
310 11902
320 11902
330 11902

say in columns A and B.

What I need is cell C2 to read "1/19/02 2:30."

How would I write the function to combine cells A2 and B2?

 

[bluedragon2]

There is a problem here:

11902 can be viewed as 1/19/02 or 11/9/02

The formula is easy, but you need a distinction of the date.

Blue

 

[westma]

The format of the number is MDdYy if that helps at all. I can't change the format because it will be data pasted from another application.

matt

 

[westma]

I got it to work using

=LEFT(B2,1)&"/"&MID(B2,2,2)&"/"&MID(B2,4,2)&" "&LEFT(A2,1)&":"&MID(A2,2,2)

in cell C2, but this only works until my time gets to 10:00 or my date gets to 10/01/02.

Is there any way to just combine the cells and specify the format using something like

=TEXT(B2, "M/D/Yy&quot&" "&TEXT(A2, "H:Mm&quot

that will just use the numbers already in the cell rather than translating the numbers to a date that the number would represent in excel code? When I use this formula I get 8/1/32 0:00 rather than 1/19/02 2:30

 

[VBOnly]

There is no shorter way. You can change the LEFT with length calculation to improve:
=LEFT(B2,len(B2)-4)&"/"&MID(B2,2,2)&"/"&MID(B2,4,2)&" "&LEFT(A2,len(A2)-2)&":"&MID(A2,2,2)
The above is not true date-time calculation, just string manipulation.