dcsimg
Results 1 to 5 of 5

Thread: Help me

  1. #1
    npminh
    Guest

    Unhappy

    Is there anybody know how to calculate the amount of Sunday between 2 Start date and End date.

  2. #2
    Fanatic Member
    Join Date
    Jan 2001
    Location
    Vietnam
    Posts
    613

    Post

    Code:
    Function CountSunday(ByVal dtStart As Date, _
                         ByVal dtEnd As Date) As Long
    
    On Error GoTo CountSunday_EH
        
        Dim vrWeekDay As Date
        Dim dtTemp As Date
        Dim lngNumberOfDay As Long
        Dim lngCounter As Long
        
        'get the number of days
        lngNumberOfDay = DateDiff("d", dtStart, dtEnd)
        dtTemp = dtStart
    
        'loop thru the day
        'if dtEnd is on Sunday it is not counted
        For lngCounter = 1 To lngNumberOfDay
            vrWeekDay = Weekday(dtTemp, vbSunday)
            If vrWeekDay = vbSunday Then
                'increament if it is Sunday
                CountSunday = CountSunday + 1
            End If
            dtTemp = DateAdd("d", 1, dtTemp)
        Next lngCounter
        
    Error_Exit:
        Exit Function
        
    CountSunday_EH:
        MsgBox "Error: " & Err.Number & vbCrLf & _
               Err.Description, vbCritical, App.Title
        Resume Error_Exit
    
    End Function
    Hope this helps
    TheBao
    Last edited by TheBao; Feb 5th, 2001 at 01:09 AM.

  3. #3
    Member gs1964's Avatar
    Join Date
    Jan 2001
    Location
    Greece
    Posts
    55
    hi

    use a loop statement for startday to end day.
    and for each day: if format(mydate, "dddd")="Sunday" then i=i+1
    George
    gioste@mailbox.gr

  4. #4
    PowerPoster Chris's Avatar
    Join Date
    Jan 1999
    Location
    K-PAX
    Posts
    3,238
    Less code

    Code:
    Option Explicit
    
    Private Sub Command1_Click()
        MsgBox GetDay(DTPicker1.Value, DTPicker2.Value)
    End Sub
    
    Private Function GetDay(ByVal date1 As Date, ByVal date2 As Date) As Long
        Dim tSunday As Long
        Dim tWeek As Long
    
        tWeek = DateDiff("ww", date1, date2)
        If Weekday(date1) = 1 Then
            tSunday = 0
        Else
            tSunday = tWeek
        End If
    
        If Weekday(date2) = 1 Then tSunday = tSunday - 1
        
        GetDay = tSunday
    End Function
    Attached Files Attached Files

  5. #5
    Guest

    Thumbs down

    Thanks all of you for helping me but Chris's code doesn't work if start date and End date is in different year.
    TheBao and gs1964 are better, but it will take much time to process with large database.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width