More Dates!!!

[jameshall]

Hello

Is it possible to calculate the week number from a given date? (i.e 1-52) This will be the basis of a report (all records that a the same user id and week number)

If so how will it be effected when the same week for the following year is entered? In theory the report will provide to weeks worth of results.

The database that I am building is a time logger, it needs to keep track of annual leave, flexi time and contracted hours, it currently works to a point but it fails to deliver an accurate subtraction of the flexi time.

Many Thanks

 

[pgk]

Hi,
You cold probably work out something from the sample that I have given below:

Private Sub Command0_Click()
Dim x As Variant
Dim n As Single
x = CDate(Now())
n = DateDiff("ww", CDate("1/1/" & Year(Date)), x)
MsgBox n+1
End Sub

Hope it helps.
With regards,
PGK

 

[LittleSmudge]

First Off - If you store the date as the Week Number then you'll need to store the Year as a separate field.

Secondly - I cannot do the actual code for what you want because of international differences in the way week numbers are calculated.
Some countries take week 1 was the week with 4 or more days in it. ( This rule then divides into those countries that take Sunday as the First day of the week or Monday as the First day of the week. )
Other countries define Week 1 as the week containing the first Sunday regardless of the number of days in that week or the preceeding week.

Basically you need to :-

Find the number of days in this year
NoOfDays = EnteredDate - "#01/January/" & Year(EnteredDate) & "# + 1

Then find how many 7s in NoOfDays
WeekCount = NoOfDays\7 ( Rem "\" is integer divide - not a typo ! )

Now add some logic to adjust WeekCount for your particular local assumptions regarding week numbering rules.



'ope-that-'elps.

G LS

 

[jameshall]

Thank You for your examples, they worked like a dream

Thanks again

James