Author |
Message |
Coyot #1 / 4
|
 Dates in week function
Hi everybody... I had to make this function to return me the Don't ask me why they just don't use the dates, instead of the week in the database!?! ...and also why there isn't a VB function for it, I only found the oposit: intWeek=Format(MyDate,"ww") So here's my function, could anyone tell me if this is trustable...or better: if it can be done easier? (it runs through the whole year comparing the values!?!?! ...and then conects both dates in a string like "dd-mm-yyyy untill dd-mm-yyyy") Public Function WeekDatum(intW As Integer) As String Dim intWeek As Integer Dim Datum As Date Dim N As Integer For N = 1 To DateDiff("d", DateSerial(Year(Now), 1, 1), DateSerial(Year(Now), 12, 31)) + 1 Datum = DateAdd("d", N, DateSerial(Year(Now) - 1, 12, 31)) intWeek = Format(Datum, "ww", vbMonday) If intWeek = intW Then WeekDatum = Format(Datum, "ddd, dd-mmm-yyyy") Exit For 'Got the 1st so get out the for End If Next N Datum = DateAdd("d", 6, Datum) 'Add 6 days to get the last date WeekDatum = WeekDatum & " t/m " & Format(Datum, "ddd, dd-mmm-yyyy") End Function I tried this too but didn't work allways (week 53???) public function WeekDatum(intW as Integer) as Date ' geeft de eerste dag (datum) van de ingegeven week WeekDatum = Format(DateAdd("ww", intW - 1, DateSerial(Year(Now), 1, 1)), "dd-mmm-yyyy") end function
|
Tue, 11 May 2004 00:58:08 GMT |
|
 |
Coyot #2 / 4
|
 Dates in week function
<correction> First part should be: I had to make this function to return me the first and last date of a week when given the number of the week. sorry ;-)
|
Tue, 11 May 2004 01:05:21 GMT |
|
 |
Johan Bechthu #3 / 4
|
 Dates in week function
The following function returns the date from the given year, week-number and day of week: ================================================= Public Function ExtractDate(iWeekday As Integer, iWeek As Integer, iYear As Integer) As Date Dim Result As Date 'To check the date of the first weekday in the first week of the year 'start looking from one week before the beginning of the year Result = DateSerial(iYear - 1, 12, 25) 'Check if the next date is in week 1 'otherwise continue the loop Do Result = Result + 1 Loop Until DatePart("ww", Result, vbUseSystemDayOfWeek, vbUseSystem) = 1 'Add the weeks and days to the found date ExtractDate = DateAdd("ww", (iWeek - 1), Result) + (iWeekday - 1) End Function ================================================= Hope this helps, Johan.
Quote: > <correction> > First part should be: > I had to make this function to return me the first and last date of a week > when given the number of the week. > sorry ;-)
|
Tue, 11 May 2004 03:44:36 GMT |
|
 |
Coyot #4 / 4
|
 Dates in week function
Great!!! Thats exactly it! Thanx
Quote: > The following function returns the date from the given year, week-number and > day of week: > ================================================= > Public Function ExtractDate(iWeekday As Integer, iWeek As Integer, iYear As > Integer) As Date > Dim Result As Date > 'To check the date of the first weekday in the first week of the year > 'start looking from one week before the beginning of the year > Result = DateSerial(iYear - 1, 12, 25) > 'Check if the next date is in week 1 > 'otherwise continue the loop > Do > Result = Result + 1 > Loop Until DatePart("ww", Result, vbUseSystemDayOfWeek, vbUseSystem) = 1 > 'Add the weeks and days to the found date > ExtractDate = DateAdd("ww", (iWeek - 1), Result) + (iWeekday - 1) > End Function > ================================================= > Hope this helps, > Johan.
> > <correction> > > First part should be: > > I had to make this function to return me the first and last date of a week > > when given the number of the week. > > sorry ;-)
|
Tue, 11 May 2004 18:05:30 GMT |
|
|
|