Sequence of dates

Help
,
1

Greetings colleagues,

I would like to kindly ask you if there is a way how to check that a sequence of DateTime variables is complete.

Imagine one column in data table with dates such as 01.01.2017, 01.02.2017, 01.03.2017…

I would like to check that the sequence is complete. In case that it will be only 01.01.2017, 01.03.2017, the robot will treat differently. I am pretty sure that there is a solution with For Each and If inside, but I am seeking for something more fancy if you understand :slight_smile:
Thanks

2

Hi.

How about if you convert the string to a date then compare the next value as a date?
For example:

str = dt1.Rows(0).Item("Date").ToString // to take first date
For each row in dt1
 
    If If(IsDate(str) And IsDate(row("Date").ToString.Trim, CDate(str).AddDays(1) = CDate(row("Date").ToString.Trim), False) // checks if next day using last row item is equal to current row item
        <do actions>

    str=row("Date").ToString.Trim // store last row item to check in if condition for next row item

Regards.

3

Sure.

Anyway, there is probably no option to avoid for each right?

4

Oh, you just simply want to determine if a date is missing before processing.
I’m fairly sure there is a way with lambda/LINQ in vb.net.

I’ll have to get back to you later, though.

5

Hi again.

So here is an approach to determining if a day is missing:

First, you need to create a list of dates between the first and last date in your table.
You can do that with Enumerable.Range, like this:

Enumerable.Range(0,CDate(dt1.Rows(dt1.Rows.Count-1).Item("Date").ToString).Subtract(CDate(dt1.Rows(0).Item("Date").ToString)).Days+1).Select(Function(d) CDate(dt1.Rows(0).Item("Date").ToString).AddDays(d) ).ToArray

Which takes a range from 0 to the subtraction between the first and last date, and uses .Select to use .AddDays with each of the numbers in the enumerable list.

Then, to compare that with your table list, you can use a .Count or .Any condition

A .Count condition would look like this:

dt1.AsEnumerable.Select(Function(row) CDate(row("Date").ToString) ).Count = Enumerable.Range(0,CDate(dt1.Rows(dt1.Rows.Count-1).Item("Date").ToString).Subtract(CDate(dt1.Rows(0).Item("Date").ToString)).Days+1).Select(Function(d) CDate(dt1.Rows(0).Item("Date").ToString).AddDays(d) ).ToArray.Count

A .Any condition would look like this:

Enumerable.Range(0,CDate(dt1.Rows(dt1.Rows.Count-1).Item("Date").ToString).Subtract(CDate(dt1.Rows(0).Item("Date").ToString)).Days+1).Select(Function(d) CDate(dt1.Rows(0).Item("Date").ToString).AddDays(d) ).ToArray.Any(Function(d) dt1.AsEnumerable.Select(Function(row) CDate(row("Date").ToString) ).ToArray.Contains(d) )

I hope this is helpful.

Regards.