Convert text date to real date

[KingTamo]

This is a date in text format "Wednesday, July 1, 2018". How can I could convert it to real date? The result is 1/7/2018

[6StringJazzer]

Try this:

Please Login or Register  to view this content.

[KingTamo]

Thanks. I got #VALUE# error.

[Rick Rothstein]

Does this formula work for you...

=0+TRIM(SUBSTITUTE(MID(A1,FIND(" ",A1,FIND(" ",A1)+1),3),","," ")&MID(A1,FIND(" ",A1)+1,3)&RIGHT(A1,5))

[KingTamo]

Thanks a lot Rick. That works. I got dash as delimeeter inside date. How could got / instead?

[6StringJazzer]

Thanks. I got #VALUE# error.

It works for the sample you provided. Please attach your file for a more specific response.

date.JPG

[Rick Rothstein]

@6StringJazzer,

I've run into the problem with your solution myself. It seems that some "standard" date formats here in the US do not work at all overseas. My recollection is that the Month Day, Year format is one of those.



@KingTamo,

My code returns a real date so you can simply Custom Format the cell with the date format of your choosing.

[6StringJazzer]

I've run into the problem with your solution myself. It seems that some "standard" date formats here in the US do not work at all overseas. My recollection is that the Month Day, Year format is one of those.

Thanks, I have had very limited experience in other localizations.

[Rick Rothstein]

I have had very limited experience in other localizations.

Me too which is why when I posted a formula in the past that outputted the date the way your formula did, I was told it did not work by that OP and someone else came along and informed me basically what I told you above.

[KingTamo]

Thanks a lot Rick. It works well now. How can I use it in the macros section?

[Rick Rothstein]

Try integrating this into your code...

Please Login or Register  to view this content.

[KingTamo]

I got a error at the last line of the code. Type mismatch.

[Rick Rothstein]

We are back to that problem with international date formats. Try replacing that line of code with this and see if that works...

RealDate = CDate(Arr(2) & "-" & Arr(1) & "-" & Arr(3))

[KingTamo]

Sorry but the same error.

[Rick Rothstein]

Okay, let's see if we can bypass VBA's attempt to form a date from a text string and do it the long way. Does this work for you...

Please Login or Register  to view this content.

[KingTamo]

Thanks a lot Rick sir. This solved the my problem.

[Rick Rothstein]

Great... I am glad we finally got this sorted out. I am going to save my solution in a file so I can reuse it should this or a similar question come up in this forum in the future.