none
Need to exclude saturday and sundays in datgridview RRS feed

  • Question

  • Hi Expert,

    Need some help to exclude the saturdays and sundays in my code. See below:

    Dim rowIndexBase As Integer = 0
            Dim cul As System.Globalization.CultureInfo = System.Globalization.CultureInfo.CreateSpecificCulture("en-US")
            'Dim plus As Integer = 2


            For rowIndexBase = 0 To DataGridView1.Rows.Count - 3
                Dim startTime As DateTime = DateTime.Parse(CType(DataGridView1.Item(15, rowIndexBase).Value, String), cul)
                'Dim endTime As DateTime = DateTime.Parse(CType(DataGridView1.Item(0, rowIndexBase + 1).Value, String), cul)

                Dim endTime As DateTime = Now
                Dim dateDiff As Integer = CInt(endTime.Subtract(startTime).TotalDays) - (DayOfWeek.Saturday AndAlso DayOfWeek.Sunday)
                DataGridView1.Item(20, rowIndexBase).Value = dateDiff.ToString
            Next

    Friday, May 10, 2019 8:43 AM

All replies

  • Hello,

    Here is an example where I populate the DataGridView with a list but does not have to be a list. A List is used to ensure each date is actually a date. In a real application when dates are present you the developer should ensure they are dates using assertion which can be done many different ways.

    The key here is a function used in a lambda statement to exclude weekend dates.

    Definition of the class to populate the DataGridView

    Public Class Item
        Public Property EventName() As String
        Public Property EventDate() As Date
    End Class

    Working example

    Public Class Form2
        Private Sub Form2_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim itemList As New List(Of Item) From
                    {
                        New Item With {.EventName = "A", .EventDate = #5/1/2019#},
                        New Item With {.EventName = "B", .EventDate = #5/2/2019#},
                        New Item With {.EventName = "C", .EventDate = #5/3/2019#},
                        New Item With {.EventName = "D", .EventDate = #5/4/2019#},
                        New Item With {.EventName = "E", .EventDate = #5/5/2019#},
                        New Item With {.EventName = "F", .EventDate = #5/6/2019#},
                        New Item With {.EventName = "G", .EventDate = #5/7/2019#},
                        New Item With {.EventName = "H", .EventDate = #5/8/2019#},
                        New Item With {.EventName = "I", .EventDate = #5/9/2019#},
                        New Item With {.EventName = "J", .EventDate = #5/10/2019#},
                        New Item With {.EventName = "K", .EventDate = #5/11/2019#},
                        New Item With {.EventName = "L", .EventDate = #5/12/2019#}
                    }
    
            DataGridView1.DataSource = itemList
    
        End Sub
    
        Private Sub excludeWeekendDates_Click(sender As Object, e As EventArgs) _
            Handles excludeWeekendDatesButton.Click
    
            Dim source = CType(DataGridView1.DataSource, List(Of Item))
            DataGridView1.DataSource = source.Where(Function(row) IsWorkDay(row.EventDate)).ToList
    
        End Sub
        Public Function IsWorkDay(sender As DateTime) As Boolean
            Select Case sender.DayOfWeek
                Case DayOfWeek.Saturday
                    Return False
                Case DayOfWeek.Sunday
                    Return False
                Case Else
                    Return True
            End Select
        End Function
    End Class

    Before and after clicking the button


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, May 10, 2019 10:14 AM
    Moderator
  • Hello,

    Just checking in to see how you are doing with this issue.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Saturday, May 18, 2019 12:17 PM
    Moderator
  • Hi Karen,

    Am still looking another way because what I want is a datediff in two columns in datagridview once the form is loaded.

    Monday, May 20, 2019 12:36 AM
  • Hi Karen,

    Am still looking another way because what I want is a datediff in two columns in datagridview once the form is loaded.

    I focused on "Need to exclude saturday and sundays", if you need to do a difference between two dates then do something like this.

    Public Class Item
        Public Property EventName() As String
        Public Property StartDate() As Date
        Public Property EndDate() As Date
        Public ReadOnly Property Difference As Long
            Get
                Return DateDiff(DateInterval.Day, StartDate, EndDate)
            End Get
        End Property
    End Class


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, May 20, 2019 12:52 AM
    Moderator
  • Hi Karen,

    Am still looking another way because what I want is a datediff in two columns in datagridview once the form is loaded.

    Hi

    You want another way! OK,please refer to your post from several months ago where you asked the same question and posted that you got a working answer!

    In case you are unable to find that thread, here is a link and an image to remind you,.

    THIS LINK


    Regards Les, Livingston, Scotland

    Monday, May 20, 2019 1:21 AM