VBA: reading date from a string of a specific format

Question

I'm looking for a way to convert strings to Date data type in VBA. I can use CDate() function for this purpose, but it uses its own implicit algorithm for parsing strings and I would like to specify my own format to differentiate between "DD/MM/YY" and "MM/DD/YY", for example.

Please notice that unlike Visual Basic, VBA has no DateTime.TryParseExact function.

ADD: As some answers suggest, I can write my own parser. If there is no built-in solution, it will be my choice.

@braX, Microsoft: CVDate is identical to CDate except for the declared type of its return value. I use CDate to get Date variable. — AlexVB
– AlexVB, Commented Aug 24, 2017 at 15:42
@GowthamShiva Let's say my string is "01/02/2017". I want to tell VBA what it is ("DD/MM/YYYY" or "MM/DD/YYYY") explicitly. — AlexVB
– AlexVB, Commented Aug 24, 2017 at 15:45

ShanayL · Accepted Answer · 2017-08-24 15:44:24Z

2

Do something like this...

strDate = Split(strDate,"/")
newstrDate = strDate(1) & "/" & strDate(0) & "/" & strDate(2)
strDate = CDate(newstrDate)

edited Aug 24, 2017 at 15:44

answered Aug 24, 2017 at 15:37

ShanayL

1,2572 gold badges14 silver badges29 bronze badges

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

Comments

Achim Gmeiner · Accepted Answer · 2017-08-24 15:46:16Z

1

In this case I would avoid CDate alltogether and instead split the date string into year, month and day and use DateSerial(year, month, day). This way you have full control.

answered Aug 24, 2017 at 15:46

Achim Gmeiner

1514 bronze badges

Comments

nishit dey · Accepted Answer · 2017-08-24 15:25:19Z

0

Can you use the below code

Msgbox Format("24/01/2017","MMM, DD, YYYY")

This will give you output as Jan, 24, 2017 . You can use other format which is easily available when you click on the Format cells. Thanks

answered Aug 24, 2017 at 15:25

nishit dey

4581 gold badge7 silver badges22 bronze badges

2 Comments

ProgrammingLlama Over a year ago

Wrong way round. Read the question again.

AlexVB Over a year ago

I suppose that this will first convert "24/01/2017" to Date data type and then format it using "MMM, DD, YYYY". I want to control the first stage so that I can specify that "01/02/2017" is February, 1 and not January, 2 (or other way round).

Collectives™ on Stack Overflow

VBA: reading date from a string of a specific format

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related