Enter weeknumber -> save begin & end date of entered week

[JiTS]

Well, I am stuck again with my weeknumber problem...

Mile-O-Phile, yes... you're right again! Damn...

[Problem]
I would like to enter a weeknumber and the begin date and end date of that week will be saved in a table.

For example:
I enter weeknumber 1 and the dates 01-01-2004 and 01-04-2004 will be saved in a table.

Is this possible to realise?!?
Other ideas are welcome too... ofcourse!

BTW I don't want to enter a date, but only a weeknumber.


Thanks,
JiTS

 

[spacepro]

This will help. The link shows a function to return the monday of the week number. You can alter the code to return the friday as well or use the dateadd function or similair to calculate the friday.

Module to convert week number

Andy

 

[ChrisO]

See if this works in your case.

Week 9 should pick up the leap year and it does.

Option Explicit
Option Compare Text


Sub Test()

    MsgBox Format(GetDateForWeek(9), "mm/dd/yyyy") & " to " & Format(GetDateForWeek(), "mm/dd/yyyy")
    
End Sub


Public Function GetDateForWeek(Optional lngWeek As Variant) As Date
    Static datMyDate As Date
    
    If IsMissing(lngWeek) Then
        GetDateForWeek = datMyDate + 3
    Else
        datMyDate = DateAdd("ww", lngWeek, DateValue("1/1"))
        datMyDate = datMyDate - WeekDay(datMyDate, vbThursday) - 6
        GetDateForWeek = datMyDate
    End If
    
End Function

I don’t know why the week starts on Thursday and ends on Sunday, is that a typo?

Regards,
Chris.

 

[raskew]

Could have been a typo, but before we can
be sure, we need to know how the user
defines:

a) The first day of the week.
b) The first week of the year

Because of the variations that can occur
based on the answers to a & b above, I’d never
advise anyone to base their calculations on
week number.

JiTS: Look at the help file explanation for the
DatePart() function (which is where one would
generate a week number).

Using the default settings:

a) First day of the week = vbSunday ‘1
b) First week of the year = ‘Start with week in which January 1 occurs’

From the debug window:

? datepart("ww", #1-Jan-2004#)
to
? datepart("ww", #3-Jan-2004#)
....returns 1, since 1-Jan-04 = Thursday and
the week starts on Sunday, so Thur, Fri & Sat
are within the first week.

However, if you modify the statement by specifying
that the first day of the week = vbMonday ‘2
you get:

? datepart(“ww”,#5-Jan-2004#, vbMonday)
returns: 2

You’ll have to play with it a while to realize the full
impact.

JiTS

Personally think that this method is so shaky, unreliable
and error prone that you need to seriously rethink your
strategy. Don’t understand why anyone would want to
use Week Number. This is not an intuitive process. It’s
kind of like saying: “...today is 29-Aug-2004, please input
the phase of the moon.”
Who the hell knows (or cares).

Stick with inputting a date. The first and last days of that
particular week can be easily calculated and there’s no
need to store them in a table since they’ll always be
available via a calculated field.

Bob

 

[ChrisO]

G’day Bob.

I totally agree but have seen this ‘mind set’ before.

Week numbers seem to come from time planners that are stuck to the wall.
That means they are a ‘flat file’ representation of upcoming events.
Weeks can be numbered which alleviates anybody, familiar with Sesame Street, from the need to learn dates.
It’s reasonably easy to understand and that’s why Managers seem to go for them.
Given that we are sometimes paid by Big Bird, it may be prudent to sing his song.

Just why Big Bird wasn’t used many years ago to test for poisonous gasses, I don’t know.

Regards,
Chris.

 

[dan-cat]

Just why Big Bird wasn’t used many years ago to test for poisonous gasses, I don’t know.

Could you imagine Big Bird going down a shaft with a bunch of coal miners

 

[JiTS]

Thanks for your time and many ideas about the weeknumber problem.

At this moment I will use DatePart and I have more time I will figure out an other solution.


BTW Big Bird from Sesame Street is called 'Pino' in The Netherlands... sounds funny for you I guess...


*SMiLE*
JiTS