Question DateDiff and Problems with FirstDayOfWeek

[paulpitchford]

Hi,

I am writing some code that is trying to determine how far through a season we are in a game we play.

For example:
13/11/2009 = week 1
20/11/2009 = week 2

The code below, sort of works:

Dim currentWeek As Long
Dim totalWeeks As Long
'We add 1 here as the datediff doesn't take into account the first 
'week we play (ie week 1 = 0)
currentWeek = DateDiff(DateInterval.WeekOfYear, DataRow("DateFrom"), GameDate) + 1
totalWeeks = DateDiff(DateInterval.WeekOfYear, DataRow("DateFrom"), DataRow("DateTo")) + 1
Return "Week " & currentWeek & " of " & totalWeeks & " weeks."

However, we may play another game in between the two dates given. The two dates happen to be Fridays. Now because vb.NET sets the first day of the week to a Monday, if we play a game Tuesday it makes that week 2, when actually it should be week 1.

Using the code below would fix my problem:

currentWeek = DateDiff(DateInterval.WeekOfYear, DataRow("DateFrom"), GameDate, FirstDayOfWeek.Friday) + 1

However, the games won't always start on a Friday, the day we start is determined by the date given from:

DataRow("FromDate")

which could be any day of the week. I've tried all sorts of things to programmatically set "FirstDayOfWeek.x", but it seems as though vb.NET wants it hard coded as FirstDayOfWeek.Monday or .Tuesday etc and you can't pass an integer like 1 or 2 so I could set it on the fly.

Please could someone help me with a solution for this problem?

Thanks,

Paul.

 

[rcombs4]

This is just coming from the top of my head and I have not thought it out so I don't know if it would work in all cases.

Could you declare a date that is the first day of the season. Then calculate the difference in days between a game and this day. Then do integer division by 7. And since that would give you a zero based system add 1.

Say the season started on 1/1/2009 a Monday.

Game 1 was 1/5/2009 a Friday. thats a difference of 4 days. 4/7 = 0. So that game is in the 1st week.

Game 2 was 1/13/2009 a Saturday. Thats a difference of 12 days. 12/7 = 1 so that game is in week 2.