Report Week Number

[BenCasey]

Hello,
In a report I have this structure:-

Week: [ ]

and the control source to display in the brackets I have:-

=Format$([MyDate],"ww",0,0)

which gives me the week number BUT this is starting at the beginning of the Calendar Year.
Is there any way to force this to have week 1 starting at the beginning of the Fiscal Year (1st April)

There is nothing in Regional Settings to do this. Maybe it might require a Function (UDF) to do it?

Any ideas?

Thanks

[Rudi]

Try the formula below...
This is untested by me, and for more info and explanation on the formula see this thread by Curious

In the control source of the Week control place this formula:
Note: It might be calculating from the fiscal week 1 Jul? You may need to edit something in the formula to offset it?

=IIf(IsDate([MyDate]),DateDiff("ww",DateSerial(Year([MyDate])-IIf([MyDate]<DateSerial(Year([MyDate]),7,1),1,0),7,1),[MyDate],4)+1,"")

[HansV]

Indeed, that formula uses July as first month (that's the significance of the 7s in the formula. For April:

=IIf(IsDate([MyDate]),DateDiff("ww",DateSerial(Year([MyDate])-IIf([MyDate]<DateSerial(Year([MyDate]),4,1),1,0),4,1),[MyDate],4)+1,Null)

[Rudi]

Indeed, that formula uses July as first month (that's the significance of the 7s in the formula. For April:

=IIf(IsDate([MyDate]),DateDiff("ww",DateSerial(Year([MyDate])-IIf([MyDate]<DateSerial(Year([MyDate]),4,1),1,0),4,1),[MyDate],4)+1,Null)

TX...I didn't study up the formula arguments.
I thought the 7's were referring to week day (as in 7 days in a week)

[BenCasey]

Thank you both for that.
Regards