[RESOLVED] DateDiff not working properly

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.

vb.net Code:

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

this should work

vb Code:

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

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

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....

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)

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

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?

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

Code:

---

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?

Code:

---

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

---

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.

vb.net Code:

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?

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).

vb.net Code:

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

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

Code:

---

        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

---