Calcute Week Numbers Starting from 1 July of Current Year (1 Viewer)

Hi everyone,

I want to calculate the week number of a date in a year starting from the 1 July (of the current year) - as this is the beginning of the Financial Year in Australia.

The starting day of the week is Wednesday.

I've been trying to think how I could use the Access DatePart Function in a query to calculate the week number from sales records.

I have had no trouble calculating the week number from the 1 Jan using the DatePart function. However, the function doesn't allow me to select a different date other than the first week of January.

So as a work around, I've thought of calculating the week number of 1 July in the current year, and just using the DateAdd function to add the week number to the week calculated by the DatePart Function.

Not quite working yet. Here's what I've come up with so far.


WeekNumberCount: DatePart("ww",[DateCalled],4)

I use the following to calculate the "my" starting week of the year:

StartingWeekInYear: DatePart("ww",DateAdd("ww",0,"1-July-" & Year(Date())),4)


Then I add the two together together:

MyWeekNumberCount: DatePart("ww",[DateCalled],4)+DatePart("ww",DateAdd("ww",0,"1-July-" & Year(Date())),4)


I know it needs some work to be useful for all years. Any suggestions?

When I'm working with Fiscal years I just add or subtract 6 months to my dates before I work with them.

THE FORMULA for - Calculating Week Numbers Starting from the Current Financial Year

Thanks Rural,

I didn't do it that way exactly, but the idea about using the Fiscal Years helped me to complete the formula to do the job.

Also, thanks to Rich for posting the following function to calculte the current Fiscal Year:

Rich said:

Public Function FinancialYear(dDate As Date) As Integer
FinancialYear = Year(dDate) - IIf(dDate < DateSerial(Year(dDate), 7, 1), 1, 0)
End Function

Click to expand...

I used Rich's function within my DatePart function to calculate the week number of [MyDate] within the current Fiscal Year.

Here's what I used in the query grid:



I had to add the +1 to the end of the DateDiff function result, to stop the function returning "0" for a week number. E.g. when the 1/Jul starts on Wednesday or after, for the first week of the Fiscal Year, the function would return a FiscalYrWeekNo = 0.

For example, the 1 Jul 2006 is on a Saturday. That means for MyWeek (starting on Wed.) I have 3 days before my MyWeek would start. Access gives me a WeekNo = 0 for this first week. I set this as WeekNo = 1 by adding the +1 to the end of my FiscalYrWeekNo function.

This was suitable for my client and consistent with the way they calculate their week numbers. Perhaps not needed for all cases.

If the last Fiscal Year ends on a Wednesday, this would be considered week number 53 for MyWeek. E.g. 30/Jun/2006.

Hope that helps someone.

Avoiding Errors with Null Date Values

I ran into some trouble as some fields had no date data in them.

I fixed the problem by adjusting the formula as follows: