How to concatenate date(mm/dd/yyyy) with time (hh:

Question

I have two cells: A1 and B1

• A1 has data in format of mm/dd/yyyy

• B1 has data in format of hh:mm:ss

When I concatenate the two cells A1 and B1 using VBA, the output contains has the format mm/dd/yyyy hh:mm. Why are the seconds not displaying? How can I fix this?

Note :

a) After concatenation the output is copied to A1.

b) I tried changing the format of date to dd/mm/yyyy, in this case its working fine .

Answer 1

It's hard to tell what you are doing exactly without seeing your code, but I think you are specifying the format of the value you are writing to cell A1: using the VBA Format function you are converting the date-time into a string.

However, to change the way the date is displayed in a cell, you need to change the format of that cell itself (not the format of the value written to it). Otherwise Excel will likely interpret the value in the cell and snap it back to the specified number format for that cell.

You can change the cell's format in the Excel 2010 window like this: Home > Number > Custom > Type: mm/dd/yy hh:mm:ss

Or, using VBA:

Range("A1").NumberFormat = "mm/dd/yy hh:mm:ss"

Answer 2

Try to change the format of C1 to mm/dd/yyyy hh:mm:ss