Week Number 2

[ZOR]

Can someone please give me the syntax to get the current week number IRRESPECTIVE OF LOCAL SETTINGS. Going up the wall on such a simple question.

 

[SkipVought]

Hi,

How 'bout

Format(MyDate, "ww")

Skip,

[red]Be advised:[/red] Alcohol and Calculus do not mix!
If you drink, don't derive!

Want to get great answers to your Tek-Tips questions? Have a look at FAQ222-2244

 

[ZOR]

Thanks, but somehow I get week 15, and as its 35 (nearly as hot in here) I am going bonkers. It seems absolutely crazy how such a basic thing as times/dates cannot be reliably calculated. I have read so many threads, seen all the pitfalls of how VB calculates with holes at the end/start of the year. I will try tommorow to see If I can get a reliable weeknumber comparison against a hardcoded number, which is unaffected by how local/regional/Bush settings are made. Thanks again.

 

[DrJavaJoe]

Don't forget to ask yourself what constitutes the first week of the year and the first day of the week. If December 31 falls on a Sunday and Sunday is the first day of the week then does that day belong to the first week of the new year or the last week of the previous year.

Two strings walk into a bar. The first string says to the bartender: 'Bartender, I'll have a beer. u.5n$x5t?*&4ru!2[sACC~ErJ'. The second string says: 'Pardon my friend, he isn't NULL terminated'.

 

[DrJavaJoe]

I see that you have, never mind. Try using the format functions other parameters Format(Date, "ww", vbSunday, vbFirstJan1).

Two strings walk into a bar. The first string says to the bartender: 'Bartender, I'll have a beer. u.5n$x5t?*&4ru!2[sACC~ErJ'. The second string says: 'Pardon my friend, he isn't NULL terminated'.

 

[ZOR]

Thanks DrJavaJoe, it seems crazy such a a basic thing can be so complex. All I want is a reliable trial cutt off period to be set. I moved from date because of all the permutations of getting it wrong against local settings, thinking a week number was an easier bet. Maybe thats why I see so many doing a count of running the application. Would I get any further sticking a hidden calendar/Datepicker on a form and evaluate that. Regards

 

[johnwm]

It sounds like your date is getting converted to a string somewhere. An alternative solution is to ensure that is doesn't get converted to string by specifically storing a number. Regardless of local settings, Date is stored (internally) as the number of days since 31 December 1899. If you store your original install date as :
[tt]
Clng(Installdate)[/tt]

you'll get 38203 today and 38204 tomorrow. So (subject to internal clock not being reset):
[tt]
Clng(Date) - Clng(InstallDate)[/tt]

will give days since installation

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[JeffTullin]

I see this has moved away from 'week number' now, to 'what is actually required'.
But in case anyone starts looking here to find a 'week number' solution, I'll contribute this now 'off topic' reply:

(Assuming we ignore questions like:
Does the year start on 1 Jan or the first Monday/Sunday of the year?
Are we talking weeks in this year or weeks into Payroll year?)

In general, just pick your start date:

Dim st as integer
st = CDate("01 Jan 2004")

and your end date
Dim eD as integer
ed = CDate("26 Jun 2004")

get the difference and divide by 7

dim dfWeekNo as double
dfWeekNo = (eD - st)/7

then round up

dfWeekNo = int(dfWeekNo + 0.5)



UK Payroll week numbers begin around 6 April each year.

 

[MichaelRed]

The one issue not mentioned (so-far) is the international setting of the date-time format:

While not EXACTLY the story above, consider:

? DatePart("ww", Date())
 32 
? Date()
8/4/2004 
? DatePart("ww", #4/8/04#)
 15

Where the first (and second) reference to date is U.S. Format (e.g. #mm/dd/yy#) while the last is to Euro format (e.g. #dd/mm/yy#)




MichaelRed
mlred@verizon.net

 

[SkipVought]

If you have a REAL DATE reflecting the ACTUAL DATE (re settings), you'll get week 32 for Now.

The only issue is where do you want week 1 to begin. So if Format(MyDate, "ww") does not begin according to your needs, then you can CALCULATE your own week similar to

MyWeek = Int((TheDate+m)/7)*7 - Int(DateSerial(Year(TheDate),1,1)) + 1

where m can be used to adjust.

And check out the anomylie during the last/first week of the year ;-)

Skip,

[red]Be advised:[/red] Alcohol and Calculus do not mix!
If you drink, don't derive!

Want to get great answers to your Tek-Tips questions? Have a look at FAQ222-2244

 

[ZOR]

Many thanks for all replies. I took DrJavaJoe's line and got week 32, which it is. So I have ended up with a quick fix for now. Be greatful for any comments of warning. I have tried setting my regional date settings and it still seems to produce the same result. However someone might see a hole. I know I have to conceal numbers in some way to prevent text editor viewing. Regards.

Dim DNB As Long, RYR As Long, RWK As Long, TheWeek As Long

TheWeek = Format(Date, "ww", vbSunday, vbFirstJan1)
RWK = 31 ' Trial Ends Week number

RYR = 2004 ' Valid year of trial
DNB = DatePart("YYYY", Now) ' Current Year

If DNB = RYR And TheWeek > RWK Then

Very crude, will not be any good at certain times of the year (re Year = Year) Did this just for now. Main thing is will it work. Thanks

 

[suoirotciv]

Just wanna tanx DrJavaJoe for the tip

I just did not notice that there is another optional part which is the FirstWeekOfYear

========================================
I kept my Job because of TEK-TIPS
Thanks a lot to all who keeps on helping others.