Problems converting string to DateTime

Question

I am making a program in which there is a function that check the database for user that haven't been called for 2 weeks or more, and shows them in a ListView.

In this part I am checking how long ago they were called:

Dim r As Int32 = excelWS.UsedRange.Rows.Count
Dim bel As New ArrayList
For nm As Int32 = 1 To r Step 1
    If Convert.ToInt32(DateDiff("ww", Date.ParseExact(excelWS.Cells(nm, 1).value(), "yyMMddhhmm", System.Globalization.DateTimeFormatInfo.InvariantInfo), Now, FirstDayOfWeek.Monday, FirstWeekOfYear.Jan1)) >= My.Settings.Tijdverschil Then
        bel.Add(nm)
    End If
Next

I get the FormatException was unhandled at the if line. In the error description it says (roughly translated to english):

The tokens aren't recognized at valid DateTime.

--edit--

If anyone thinks the format in excel is wrong, i copied one field over, they are all like this.

1408180736

Most likely is that `excelWS.Cells(nm, 1).value()` does not return a string in the format `yyMMddhhmm` — Matt Wilko, Aug 19 '14 at 13:51
Split up the complex If so you can react to what is going on. Look into using TryParse() for your logic flow. — rheitzman, Aug 19 '14 at 21:06

score 0 · Answer 1 · answered Aug 19 '14 at 13:49

0

The trick for this is include a datetime picker in your application set visibility to false.

Then set the string as value to the datetime picker and make use of the datetime picker to get the difference between dates.

answered Aug 19 '14 at 13:49

Kousick Shanmugam Nagaraj

798
2
8
26

1

you never ever need invisible UI controls to do datetime calculations. – Ňɏssa Pøngjǣrdenlarp Aug 19 '14 at 14:08

JoelC · Accepted Answer · 2014-08-19T15:04:37.613

0

Without additional information, I wonder if your date from Excel is coming in as an OLE Automation Date. Depending on how you read the data from Excel, it may come back in this format.

If it is, you need to parse it as a double and then as an OLEDate. Something like this:

        Dim oleDate as Double
        Dim result as DateTime
        If Double.TryParse(excelWS.Cells(nm, 1).value(), oleDate) Then
            ' Handle valid OLE Automation dates...
            result = DateTime.FromOADate(oleDate)
        End If

edited Aug 19 '14 at 15:04

answered Aug 19 '14 at 13:53

JoelC

3,664
9
33
38

eh i am sorry, not a pro programmer, i guess value should be the string, but what shoud oleDate be? – mielleman Aug 19 '14 at 14:11
I updated my answer to hopefully be clearer. In theory, you will want to bring your Excel cell value (if it is in fact an OLEDate) and convert that value. – JoelC Aug 19 '14 at 15:07

Problems converting string to DateTime

2 Answers2