Current week lable

[torquay]

Hi I am trying to create a lable for a report showing the current week but I cannot seem to get it right.
I have found this on Microsoft but it has the field [Salesdate]. As my lable isn't based on anything I cannot seem to get it to work

Year([SalesDate])* 53 + DatePart("ww", [SalesDate]) = Year(Date())* 53 + DatePart("ww", Date()) - 1

Thanks

 

[gemma-the-husky]

look at FORMAT function

this will compute a week no. directly, if required - is that what you mean?

 

[torquay]

I am looking for the lable to be something like
April 20th - April 24th (today) in otherwords current week starting Sunday (April 20th) to today's date (April 24th)
Thanks

 

[namliam]

This is calculatable, why make a table for it?

 

[torquay]

This is not going in a table, this is in the lable at the top of the report showig the report name and the curent week date. Hope this make sense.

 

[namliam]

This function will calculate the startday of any weeknumber.

Function WeekStart(intWeekNR As Integer, intYear As Integer) As Date
    Dim dtMondayWk1 As Date
    Dim dtJan1st As Date
    Dim intWeekday As Integer
    dtJan1st = DateSerial(intYear, 1, 1)
    intWeekday = vbMonday ' Week startday, use vbSunday for sunday
    ' Default for "week1"
    dtMondayWk1 = dtJan1st - Weekday(dtJan1st, intWeekday) + 1
    If Format(dtJan1st, "WW") <> "1" Then
        dtMondayWk1 = dtMondayWk1 + 7
    End If
    WeekStart = dtMondayWk1 + (intWeekNR - 1) * 7
End Function

 

[Darth Vodka]

This function will calculate the startday of any weeknumber.

Function WeekStart(intWeekNR As Integer, intYear As Integer) As Date
    Dim dtMondayWk1 As Date
    Dim dtJan1st As Date
    Dim intWeekday As Integer
    dtJan1st = DateSerial(intYear, 1, 1)
    intWeekday = vbMonday ' Week startday, use vbSunday for sunday
    ' Default for "week1"
    dtMondayWk1 = dtJan1st - Weekday(dtJan1st, intWeekday) + 1
    If Format(dtJan1st, "WW") <> "1" Then
        dtMondayWk1 = dtMondayWk1 + 7
    End If
    WeekStart = dtMondayWk1 + (intWeekNR - 1) * 7
End Function

Function WeekStart2(intWeekNR As Integer, intYear As Integer) As Date
    WeekStart2 = DateSerial(intYear, 1, 1) + (intWeekNR - 1) * 7 - 1
End Function

 

[namliam]

Sorry darth but I think that will not work.
Jan 1st is not allways week# 1, sometimes it is week #2, atleast here in Contintal Europe

Also you are returning ANY day that was Jan 1st as well. This year that would be Tuesday, but my function returns allways a monday.

 

[Brianwarnock]

Jan 1st is not allways week# 1, sometimes it is week #2, atleast here in Contintal Europe

QUOTE]

How ?

But I agree Darth's only works in 2008

Brian

 

[Darth Vodka]

fair enough. but i find that weeks are bespoke to each company. when does jan 1 lie in week 2, do you have 53 weeks that year etc etc

most companies just have big calendar lookup tables

how does yours know if jan 1 is in the first week or not?

 

[Darth Vodka]

Function WeekStart2(intWeekNR As Integer, intYear As Integer) As Date
    WeekStart2 = DateSerial(intYear, 1, 1) - ((DateSerial(intYear, 1, 1) + 5) Mod 7) + (intWeekNR - 1) * 7
End Function

owzat? (assumes week 1 is the week containing 1/1/YYYY)

 

[Brianwarnock]

Yep that works, or atleast it gives the same results as namliam's, and looks good to a dumbass like me.

Brian

 

[Darth Vodka]

I am looking for the lable to be something like
April 20th - April 24th (today) in otherwords current week starting Sunday (April 20th) to today's date (April 24th)
Thanks

?(date()-(date()+6) mod 7) & " to " & date()
27/04/08 to 28/04/08

 

[namliam]

How ?

owzat? (assumes week 1 is the week containing 1/1/YYYY)

Still no luck, as it still gives back a thursday.... Instead of the monday...
Also I find it pretty criptic, tho I understand it.... I dont know if everyone else does.

Week# 1 for us is the first week with 4 days in the new year
There are actualy 3 methods of calculation week#1 across the globe and all in integrated into the format function:
vbFirstJan1 1 Start with week in which January 1 occurs (default).
vbFirstFourDays 2 Start with the first week that has at least four days in the year.
vbFirstFullWeek

You obviously use vbFirstJan1, we use vbFirstFourDays. Not sure what countries/regions use first full week....
This has lead to many a problem across continents/countries when i.e. dutch are talking to Americans as a lot of times our week numbers are the same, but sometimes they are off by 1 week.... THAT is a pain...

Yes a lot of people/companies are "lazy" and build a calander of their own. I believe in functions a lot more as they dont need updating. Even if you build a calander for 10 years, I have seen a lot of systems go "broke" because the 11th year doesnt excist and nobody knows this fact.
Most systems are designed for 5-10 years, but end up having a life time of 20.

 

[Brianwarnock]

vbFirstFourDays 2 Start with the first week that has at least four days in the year.
vbFirstFullWeek

But wont using these mean that Jan 1st is in week -1 or the last week of the previous year, not week 2 ?

Brian

 

[Darth Vodka]

Still no luck, as it still gives back a thursday.... Instead of the monday...

does it? i get it giving exactly the same answers as yours? and so did Brian?

?WeekStart(3,2006)
09/01/06 
?weekstart2(3,2006)
09/01/06

Also I find it pretty criptic, tho I understand it.... I dont know if everyone else does.

fair comment, i was going for brevity over readibility

Yes a lot of people/companies are "lazy" and build a calander of their own

i see it more as malleability than laziness. a hard coded function means your sales months/weeks are controlled by IT, in real life Sales dictate when your sales weeks are, and they may come up with something funny. all depends on what the business asks for

a bit OT anyway, i don't think Torquay was asking for weeks-->start dates

 

[Rabbie]

Sorry darth but I think that will not work.
Jan 1st is not allways week# 1, sometimes it is week #2, atleast here in Contintal Europe

Also you are returning ANY day that was Jan 1st as well. This year that would be Tuesday, but my function returns allways a monday.

Namlian, As far as I know most of Europe uses the definition of week numbers in ISO 8601.
In this definition Jan 1 will either be in Week 1 or Week 52/ Week 53 of the previous year.

Fdefinition is

Mutually equivalent definitions for week 01 are:

the week with the year's first Thursday in it
the week with 4 January in it
the first week with the majority (four or more) of its days in the starting year
the week starting with the Monday in the period 29 December – 4 January
If 1 January is on a Monday, Tuesday, Wednesday or Thursday, it is in week 01. If 1 January is on a Friday, Saturday or Sunday, it is in week 52 or 53 of the previous year.

Hope this helps

 

[Brianwarnock]

does it? i get it giving exactly the same answers as yours? and so did Brian?

I actually created a table of weeknumbers and years over a few years and ran both in a query, both gave the same answer each time, and yes Darth's isn't readily obvious to me but that was just a challenge to anybody who can read help, anyway non coders will struggle with both.

Thanks for confirming my suspicions about Jan 1st Rabbie.

Brian

 

[Rabbie]

Thanks for confirming my suspicions about Jan 1st Rabbie.

Brian

You're welcome. When working and living in Sweden I found it a little disconcerting that Jan 1 was not always in week 1 so it stuck in my memory and a quick google provided the answer.

 

[namliam]

But wont using these mean that Jan 1st is in week -1 or the last week of the previous year, not week 2 ?

Brian

Did I actually say that ???

Yes I did... No that is obviously wrong! It makes Jan 1st in week# 52 or 53 of last year... like Rabbie said.

does it? i get it giving exactly the same answers as yours? and so did Brian?

So it does... just goes to show I cannot read coding

And as far as finance and administration goes... Sure they can come up with a lot of stuff.... but all is possible to catch in a function.
Only time you "run into problems" is when you want to do different selections easily, for that a related calander table can be hugely usefull but that is ussually in reporting oriented systems like a DWH IMHO.