By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,835 Members | 1,391 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,835 IT Pros & Developers. It's quick & easy.

Datepart - unable to set first week to Nov 1 to calc # of weeks

P: 1
I want to use the Datepart function to determine the week number based on my company's fiscal calendar which starts Nov. 1.

I have tried the following and they all produce #ERROR# .

DatePart("ww",[Change Calendar qry 1]![Target Date],2,"11/01/2005")
DatePart("ww",[Change Calendar qry 1]![Target Date],2,#11/1/2005#)

If leave the Nov 1 date out, I get a result that appears to be based on Jan 1 as the date. It also appears to be the week number in the year, such that Jan 1 in 2005 and 2006 both have a week number of 1.

The end result I'm looking for the following results
Nov 1, 2005, results in week number 1
Nov 1, 2006, results in week number 53.
Any help or insight would be appreciated.
Thanks
Nov 1 '06 #1
Share this Question
Share on Google+
9 Replies


NeoPa
Expert Mod 15k+
P: 31,306
So you will always want the week number to be relative to 1 Nov 2005?
Nov 1 '06 #2

NeoPa
Expert Mod 15k+
P: 31,306
Try :-
Expand|Select|Wrap|Line Numbers
  1. WeekNo = (DateDiff("d", #1 Nov 2005#, [Change Calendar qry 1]![Target Date]) \ 7) + 1
BTW
firstweekofyear Optional.A constant that specifies the first week of the year. If not specified, the first week is assumed to be the week in which January 1 occurs.
Expand|Select|Wrap|Line Numbers
  1. Constant        Value    Description
  2. vbUseSystem    0    Use the NLS API setting.
  3. vbFirstJan1    1    Start with week in which January 1 occurs (default).
  4. vbFirstFourDays    2    Start with the first week that has at least four days in the new year.
  5. vbFirstFullWeek    3    Start with first full week of the year.
Nov 1 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
It won't work because this function only allows you to start the year on Jan 1.

As a work around:-

Expand|Select|Wrap|Line Numbers
  1.  
  2.     If Month(Date) >= 11 Then
  3.         WeekNo = DatePart("ww", [Change Calendar qry 1]![Target Date]) - DatePart("ww", "01/11/" & Year(Date))
  4.     Else
  5.         WeekNo = DatePart("ww", [Change Calendar qry 1]![Target Date]) + (52 - DatePart("ww", "01/11/" & Year(Date))
  6.     End If
  7.  
  8.  
Nov 1 '06 #4

P: 21
It won't work because this function only allows you to start the year on Jan 1.

As a work around:-

Expand|Select|Wrap|Line Numbers
  1.  
  2.     If Month(Date) >= 11 Then
  3.         WeekNo = DatePart("ww", [Change Calendar qry 1]![Target Date]) - DatePart("ww", "01/11/" & Year(Date))
  4.     Else
  5.         WeekNo = DatePart("ww", [Change Calendar qry 1]![Target Date]) + (52 - DatePart("ww", "01/11/" & Year(Date))
  6.     End If
  7.  
  8.  
How about using =Ceiling((now()-#01/11/05#)/7,1)
Nov 2 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
How about using =Ceiling((now()-#01/11/05#)/7,1)
Ceiling and Floor are Excel Functions. You can attach the Excel library to use then or simply add the functions to a module in your code.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Public Function Ceiling(ByVal X As Double, Optional ByVal Factor As Double = 1) As Double
  3.     ' X is the value you want to round
  4.     ' is the multiple to which you want to round
  5.     Ceiling = (Int(X / Factor) - (X / Factor - Int(X / Factor) > 0)) * Factor
  6. End Function
  7.  
  8. Public Function Floor(ByVal X As Double, Optional ByVal Factor As Double = 1) As Double
  9.     ' X is the value you want to round
  10.     ' is the multiple to which you want to round
  11.     Floor = Int(X / Factor) * Factor
  12. End Function
  13.  
  14.  
Nov 7 '06 #6

NeoPa
Expert Mod 15k+
P: 31,306
It won't work because this function only allows you to start the year on Jan 1.
You should try it - You may just be surprised.

WeekNo = (DateDiff("d", #1 Nov 2005#, [Change Calendar qry 1]![Target Date]) \ 7) + 1
will give you exactly what you need.
Nov 7 '06 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
You should try it - You may just be surprised.

WeekNo = (DateDiff("d", #1 Nov 2005#, [Change Calendar qry 1]![Target Date]) \ 7) + 1
will give you exactly what you need.
I meant the DatePart function Adrian not DateDiff...

Mary
Nov 7 '06 #8

NeoPa
Expert Mod 15k+
P: 31,306
I meant the DatePart function Adrian not DateDiff...

Mary
It's a day for misunderstandings ;)
My bad.
-Adrian.
Nov 8 '06 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
It's a day for misunderstandings ;)
My bad.
-Adrian.
That was an easy one to make.

Don't feel bad about the other it's been resolved with all parties happy.

Mary
Nov 8 '06 #10

Post your reply

Sign in to post your reply or Sign up for a free account.