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

[Cliff]

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

[NeoPa]

So you will always want the week number to be relative to 1 Nov 2005?

[NeoPa]

Try :-

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.

[B][U]Constant		Value	Description[/U][/B]
[I][B]vbUseSystem[/B]	0[/I]	Use the NLS API setting.
[I][B]vbFirstJan1[/B]	1[/I]	Start with week in which January 1 occurs (default).
[I][B]vbFirstFourDays[/B]	2[/I]	Start with the first week that has at least four days in the new year.
[I][B]vbFirstFullWeek[/B]	3[/I]	Start with first full week of the year.

[MMcCarthy]

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

As a work around:-

 
	If Month(Date) >= 11 Then
		WeekNo = DatePart("ww", [Change Calendar qry 1]![Target Date]) - DatePart("ww", "01/11/" & Year(Date))
	Else
		WeekNo = DatePart("ww", [Change Calendar qry 1]![Target Date]) + (52 - DatePart("ww", "01/11/" & Year(Date))
	End If

[VALIS]

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

As a work around:-

 
	If Month(Date) >= 11 Then
		WeekNo = DatePart("ww", [Change Calendar qry 1]![Target Date]) - DatePart("ww", "01/11/" & Year(Date))
	Else
		WeekNo = DatePart("ww", [Change Calendar qry 1]![Target Date]) + (52 - DatePart("ww", "01/11/" & Year(Date))
	End If

How about using =Ceiling((now()-#01/11/05#)/7,1)

[MMcCarthy]

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.

 
Public Function Ceiling(ByVal X As Double, Optional ByVal Factor As Double = 1) As Double
	' X is the value you want to round
	' is the multiple to which you want to round
	Ceiling = (Int(X / Factor) - (X / Factor - Int(X / Factor) > 0)) * Factor
End Function
 
Public Function Floor(ByVal X As Double, Optional ByVal Factor As Double = 1) As Double
	' X is the value you want to round
	' is the multiple to which you want to round
	Floor = Int(X / Factor) * Factor
End Function

[NeoPa]

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.

[MMcCarthy]

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

[NeoPa]

I meant the DatePart function Adrian not DateDiff...

Mary

It's a day for misunderstandin gs ;)
My bad.
-Adrian.

[MMcCarthy]

It's a day for misunderstandin gs ;)
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