Date formatting in VBA

Question

I have a date: SAT 29 JUN 19. How do I convert it into date format e.g. 29 JUN 19 using Excel-VBA. Thanks.

By using this code:
wb.ActiveSheet.Cells(erow, 1) = CDate(Format(RemoveWkDay, "DD MMM YY"))
The output is: 29/06/2019

What's the value of RemoveWkDay? And isn't the output what you wanted - A date? — AAA
– AAA, Commented Jun 29, 2019 at 17:35
In this example the value of RemoveWkDay is 29 JUN 19 which is in general format.I want to convert it into date but preserve the same format DD MM YY.The cell A1 to A30 contains date for the month of June 2019 in an random order.My program will sort it out in an asecending order. — Rocky
– Rocky, Commented Jun 29, 2019 at 17:47
Exchange CDate and Format: Start with CDate first and then do the Format. — Asger
– Asger, Commented Jun 29, 2019 at 23:53

AAA · Accepted Answer · 2019-06-30 00:00:35Z

1

Based on your comments above, you can use:
wb.ActiveSheet.Range("A1:A30").NumberFormat = "dd mmm yy;@"
To do that for the whole column, use Range("A:A") instead.

In fact, you do not need VBA for this. You can just set that string as the custom format in the Sheet.

edited Jun 30, 2019 at 0:00

answered Jun 29, 2019 at 18:04

AAA

3,6801 gold badge18 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

AAA Over a year ago

@Asger, Yes, I did. Thank you. I have amended answer. OP hasn't bothered to say if it solved the problem..lol

Collectives™ on Stack Overflow

Date formatting in VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related