Unusual Date Format in a text file

simon4amiee · Jan 5, 2015

Someone decided to be clever and send a text file with a stupid date format, they know they are being stupid but without pointing out the obvious and going back to ask them to send me the right date format, I wondered is it possible to show off a littl ehere and get a working date format from this badboy bearing in mind it’s a text file I’m working with: Dec 18 2014 01:12PM.

Not that is an exactly cut/paste so spaces are as they are here, ideally would love to get 18/12/14 13:12.

pr2-eugin · Jan 5, 2015

I really am lost ! What are you trying to do exactly?

Code:

? CDate("Dec 18 2014 01:12PM")
18/12/2014 13:12:00

jdraw · Jan 5, 2015

Here's a small routine that works with your sample data.

Code:

Sub test77x()
Dim str As String
Dim rev As String
str = "Dec 18 2014 01:12PM."
str = Mid(str, 1, Len(str) - 1)  'remove the trailing .
rev = CDate(Format(str, "general date"))
Debug.Print rev
End Sub

to give
18/12/2014 1:12:00 PM
Good luck.

simon4amiee · Jan 8, 2015

jdraw said:
Here's a small routine that works with your sample data.

Code:

Sub test77x() Dim str As String Dim rev As String str = "Dec 18 2014 01:12PM." str = Mid(str, 1, Len(str) - 1) 'remove the trailing . rev = CDate(Format(str, "general date")) Debug.Print rev End Sub

to give
18/12/2014 1:12:00 PM
Good luck.

How do I apply this function to a table eg called Table4

namliam · Jan 8, 2015

Please dont open 2 posts for the same problem:
http://www.access-programmers.co.uk/forums/showthread.php?t=273280

Unusual Date Format in a text file

simon4amiee

Registered User.

pr2-eugin

Super Moderator

jdraw

Super Moderator

simon4amiee

Registered User.

namliam

The Mailman - AWF VIP

Similar threads

Users who are viewing this thread