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

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 .

When i do double click on output cell , seconds are appearing but normally its not appearing. — Neli S Gautham Raj, Nov 05 '13 at 03:09

sam092 · Answer 1 · 2013-11-05T08:08:58.053

1

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

edited Nov 05 '13 at 08:08

answered Nov 05 '13 at 03:12

sam092

1,325
1
8
8

dd/mm/yyyy is working .. but I need in mm/dd/yyyy ..In case of mm/dd/yyyy , its not working – Neli S Gautham Raj Nov 05 '13 at 03:41
What do you mean by not working? `mm/dd/yyyy hh:mm:ss` also works – sam092 Nov 05 '13 at 03:45
Output is coming as mm/dd/yyyy hh:mm seconds are not displayed .. – Neli S Gautham Raj Nov 05 '13 at 05:45
Thanks @sam092 ..as mentioned below it was issue with number format . – Neli S Gautham Raj Nov 05 '13 at 08:36
okay:) kinda weird. coz that line is doing the exact same thing – sam092 Nov 05 '13 at 08:48

score 1 · Accepted Answer · answered Nov 05 '13 at 08:05

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"

Thank you very much .Got it ..Range("A1").NumberFormat = "mm/dd/yy hh:mm:ss" worked . — Neli S Gautham Raj, Nov 05 '13 at 08:31

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

2 Answers2

Linked