CODEWITHSUNDEEP
vb.netdatetimeformatting
LBPLC
LBPLC

Reputation: 1571

DateTime.ParseExact was not recognized as a valid DateTime

I'm trying to convert a String to a Date format using this code:

 Debug.Print(Trim(oTable.Cell(i + 1, 2).Range.Text.Replace(vbCr, "").Replace(vbLf, "")))
 Tabledate = DateTime.ParseExact((Trim(oTable.Cell(i + 1, 2).Range.Text.Replace(vbCr, "").Replace(vbLf, ""))), "dd/MM/yy", CultureInfo.InvariantCulture)

the Debug.Print line gives me the result 30/12/15, but the DateTime.ParseExact line always returns an error saying the string was not a valid DateTime format? Am I missing something?

Note: I have to remove trailing return carragies because the string is coming from a table in word, and for some reason pulls out a return on the end of the string

UPDATE 1

Resorting to desperate measures, I've included the following in my code:

For Each A As Char In oTable.Cell(i + 1, 2).Range.Text
    Debug.Print(A)
Next

This is printing the result:

3
0
/
1
2
/
1
5
(extra blank char)
(extra blank char)

I have no idea where or what these blank characters are, I've tried replacing vbTab with nothing too but it makes no difference. It might be worth noting that the String originally comes from a Table in word, could the fact its in a cell on a table have anything to do with what these blank characters are?

UPDATE 2

Changed the debug code to:

For Each A As Char In oTable.Cell(i + 1, 2).Range.Text
    Debug.Print(CInt(AscW(A)))
Next

Returned:

51
48
47
49
50
47
49
53
13
7

which I believe means 13 is a carriage return (even though it should have been trimmed out) and 7 is a "bell" (I have no idea what a bell is)

Upvotes: 1

Views: 681

Answers (2)

Tim Schmelter
Tim Schmelter

Reputation: 460208

The old VB function Trim removes only spaces from the beginning and the end. Use the .NET method String.Trim instead which removes all kind of whitespaces like also tab-characters.

Dim celltext =  oTable.Cell(i + 1, 2).Range.Text.Replace(vbCr, "").Replace(vbLf, "")
Tabledate = DateTime.ParseExact(celltext.Trim(), "dd/MM/yy", CultureInfo.InvariantCulture)

Note that you also could use DateTime.TryParseExact if the input can contain invalid dates. Then no exception happens but the method returns False.

Since you have the carriage return(not at the end) and the bell(at the end) you might want to use this approach which should remove all unwanted characters:

Dim celltext = oTable.Cell(i + 1, 2).Range.Text.Trim()
Dim validChars = From c In celltext Where Char.IsDigit(c) OrElse Char.IsPunctuation(c)
celltext = New String(validChars.ToArray())
Tabledate = DateTime.ParseExact(celltext, "dd/MM/yy", CultureInfo.InvariantCulture)

You need to add Imports System.Linq to the top of the code file.

Upvotes: 4

Frederick
Frederick

Reputation: 169

I believe that the string produced is not in a correct pattern. Please note that CultureInfo.InvariantCulture needs a certain datetime formatting to make it work.

CultureInfo c1 = CultureInfo.InvariantCulture;

Console.WriteLine( c1.DateTimeFormat.ShortDatePattern.ToString());
Console.WriteLine( c1.DateTimeFormat.LongDatePattern.ToString());

You will see the format needed to use are the following

MM/dd/yyyy
dddd, dd MMMM yyyy

The DateTime.ParseExact will throw a FormatException because standard short date pattern or long date pattern is not followed.

Upvotes: 0

Related Questions