Merging Date and Time from two different cells into one...

[NateW]

Hi, Folks.

Cell A1 = 11/6/2007
Cell B1 = 13:45:00

I need column C1 = 11/6/2007 13:45:00

I am using the "&" operator to merge the cells, but the formatting isn't coming across properly, giving me a weird value for the time.

Cell A1 is formatted as: *3/14/2001
Cell B1 is formatted as: h:mm
Cell C1 is formatted as: 3/14/01 13:30

Here is my code:



Sheets("gate Control").Cells(varMasterRow, 6).Value = _
Workbooks(varWorkbook).Sheets(varSheet).Cells(varRow, 1).Value & _
Workbooks(varWorkbook).Sheets(varSheet).Cells(varRow, 2).Value



If Cell A1 = 10/29/2007
and Cell B1 = 8:00:00 AM,
the result I'm getting for C1 = 10/29/20070.333333333333333

Can anyone help me get that to = 10/29/2007 8:00
?

Thanks in advance!!

[NateW]

By the way, I tried to use "+", but I get an Error 13 - Type Mismatch. I thought + would work, but maybe it's in the way they are formatted?

Again, any help would be so appreciated - thanks!

[tpoynton]

I think that putting it into a string will do the trick...untested, but if I'm wrong, someone will point out my errors!


Dim sDateTime as String
sDateTime=Workbooks(varWorkbook).Sheets(varSheet).Cells(varRow, 1).Value & " " &_
Workbooks(varWorkbook).Sheets(varSheet).Cells(varRow, 2).Value

Sheets("gate Control").Cells(varMasterRow, 6).Value = sDateString


EDIT - also discovered that a having a formula in C1 (A1 + B1), then formatting it with the appropriate time setting (including date and time, at the bottom of the list of time formats) works too.

[rory]

I suspect your date is actually entered as text. Try this:
With Workbooks(varWorkbook).Sheets(varSheet)
Sheets("gate Control").Cells(varMasterRow, 6).Value = _
CDate(.Cells(varRow, 1).Text & " " & .Cells(varRow, 2).Text)
End With