[RESOLVED] DateDiff not working properly

[tgf-47]

I need to calculate the number of weeks between 2 dates. This will be used in a billing system.

If something comes in on Thursday, they must get billed for that whole week.

Here is my code that I tried. The code gives me wrong values. Only on the 3rd week, my week count is 1.

Private Function DIFF(ByVal THEDATE As Date)
        Dim i As Integer
        i = DateDiff("ww", THEDATE, Now.Date, FirstDayOfWeek.Monday)
        Return i
    End Function

Honestly I cant see why this isn't working properly. Probably my lack of experience. Can anyone help me here?

The code must be able to calculate the number of weeks between
12/15/2008 - 30/10/2010 for example

[aashish_9601]

this should work

Private Function diff(ByVal TheDate As Date) As Integer
        Return CInt(DateDiff(DateInterval.Weekday, TheDate, Now.Date, FirstDayOfWeek.Monday, FirstWeekOfYear.System))
    End Function

[tgf-47]

Your code works much better. Except now it only skips 1 week.


My logic tells me, since the beginning of the week is Monday. Setting the date to the 1st which is a Thursday from the previous week. The week count should be one, right?

I must be overlooking something here....

[tgf-47]

I just saw something interesting. If I set it one day back(30'th), then it counts it as a week. So vb is just ignoring the first day of week property and just counts the days... 7 days = 1 week.

Now does anyone have a better way of making it do what i need it to do? (Week Difference)

[aashish_9601]

the output is correct, from 1st jul to 7th just you are still in the first week only, the week has not yet completed, that is why it returns 0. when you go to 8th july then one week will be completed

[tgf-47]

Yes I understand that.

I need a way to count weeks, without the day factor.

(monday = new week)
Do you have any idea how i can achieve this?

[Siddharth Rout]

Not sure why vb.net is behaving in this manner but in vb6, I do get 1 as the week difference

Private Sub Command1_Click()
    d1 = #7/1/2010#
    d2 = #7/7/2010#
    
    MsgBox DateDiff("ww", d1, d2, vbMonday)
End Sub

Alternatively what you can do is get individual weeknumber and subtract them?

Dim weeknumber As Integer = DatePart(DateInterval.WeekOfYear, THEDATE)

[tgf-47]

I want this to be as accurate as possible as I'm working with peoples money here.

Since we bill per week and for full weeks only, I think I should just set the arrival date to the first day of the week that it came in and set my current date to the last day of my current week.

date1 = DateAdd("d", 0 - date1.DayOfWeek, date1)

This would be perfect, but how do i set the default first day of the week for the above code to Monday instead of Sunday?

[tgf-47]

I got it working!

What is happening is the "begin date" goes back to the first day of that week (Monday) and todays date, the date that the bill will be calculated to is the last day of this week (Sunday).

Private Function diff2(ByVal date1 As Date, ByVal date2 As Date) As Integer
        If date1.DayOfWeek = DayOfWeek.Sunday Then
            date1 = date1.AddDays(-1)
        End If
        If date2.DayOfWeek = DayOfWeek.Sunday Then
            date2 = date2.AddDays(-1)
        End If
        date1 = DateAdd("d", 1 - date1.DayOfWeek, date1) '\\\\ FIRST DAY (MONDAY) OF THAT WEEK
        date2 = DateAdd("d", 6 - date2.DayOfWeek, date2) '\\\\ LAST DAY (SUNDAY) OF THAT WEEK
        date2 = date2.AddDays(1)
        Return CInt(DateDiff(DateInterval.Weekday, date1, date2, vbMonday, FirstWeekOfYear.Jan1))
    End Function

[dbasnett]

This computes weeks between two days. Partial weeks are treated as full weeks

        Dim sDT As DateTime = #6/30/2010#
        Dim eDT As DateTime = #7/14/2010#
        Dim ts As TimeSpan
        Dim numWeeks As Integer

        Debug.WriteLine("")
        For x As Integer = 0 To 13

            ts = eDT - sDT.AddDays(x - 1) 'calculate days

            numWeeks = CInt(Math.Ceiling(ts.TotalDays / 7))

            Debug.Write(sDT.AddDays(x).ToShortDateString & " " & eDT.ToShortDateString)
            Debug.WriteLine(" " & numWeeks.ToString)

        Next