Accounting Year Week Calculations ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction.

Many companies maintain their Accounting Year from April 1 to March 31 of the following year. If you need a weekly analysis of sales or other activities, the challenge is that the standard DatePart() function in Microsoft Access calculates week numbers starting from January 1, not from April 1. For accounting purposes, April 1–7 should be considered Week 1, April 8–14 Week 2, and so on, regardless of the calendar week number.

We have the built-in Function DatePart() in Microsoft Access to calculate Week numbers based on the activity date provided to the function as a parameter. Let us try an example of the DatePart() function directly on the Debug Window.

The date will be passed in dd-mm-yyyy format, and Sunday is taken as the first day of the week. The VBA Constant vbSunday represents the numeric value 1.

dt = DateValue("01-04-2011")
? DatePart("ww",dt,vbSunday)

Result: 14

The built-in DatePart() Function returns week number 14 instead of 1 for the accounting week period of April 1–7. This result can fluctuate between 13 and 14, depending on the first day of the week (the second parameter in the function, vbSunday in this case) and the year. For example, using DatePart() April 1, 2006, returns week 13, not week 1, which shows why an adjustment is needed to calculate accounting week numbers accurately starting from April 1.

To create a weekly Sales Graph for the first quarter of the accounting year (the first 13 weeks covering April, May, and June), we first need to convert the sales dates into their corresponding week numbers. This allows us to aggregate the sales values into weekly totals for the chart.

In short, the DatePart() function cannot be used directly to calculate Accounting months or weeks without some modifications. We can use the DatePart() function within our own Code to modify the output we want.

Function: AccWeek().

I have created a function, AccWeek(), to calculate week numbers for an accounting year running from April 1 to March 31. This function is primarily designed to be called from a query column, with an activity date (such as a sale date, payment date, etc.) as the parameter, to return the corresponding accounting week number. You can also use this function in VBA code, on a form, report, or anywhere else you need to determine the accounting week.

Copy and paste the following Code into a Standard Module in your Database and save it:

Public Function AccWeek(ByVal accDate As Date) As Integer
'--------------------------------------------------------------
'Author : a.p.r.pillai
'Date   : June 2012
'All Rights Reserved by www.msaccesstips.com
'--------------------------------------------------------------
Dim wkdayStart As Integer, wk As Integer, wkout As Integer
Dim accStart As Date, wksave As Integer, accStart1, accStart2

On Error GoTo AccWeek_Err
'First day of the week is taken Sunday as default
'If change is needed in your area please change the next line
wkdayStart = vbSunday
'calculate week number with built-in function
wk = DatePart("ww", accDate, wkdayStart)
'modify the week number according to accounting period
wksave = IIf(wk = 13 And ((Year(accDate) - 1) Mod 4) = 0, wk + 1, 13)
Select Case wk
      Case Is <= 13, 14
         wkout = DatePart("ww", DateValue("31-12-" & Year(accDate)), vbSunday) - wksave + wk
      Case Is > wksave
         wkout = wk - wksave
End Select

accStart1 = "01-04-" & Year(accDate)
accStart2 = "08-04-" & Year(accDate)

'Overlapped Week days check and reset week to 1
If (accDate >= accStart1) And (accDate < accStart2) Then
   wk = DatePart("ww", accDate, vbSunday)
   If wk > 1 Then
     wkout = 1
   End If
End If
AccWeek = wkout

AccWeek_Exit:
Exit Function

AccWeek_Err
MsgBox Err.Description, , "AccWeek()"
Resume AccWeek

End Function

Usage Example-1:

Calling AccWeek() Function from a Query Column:

SaleWeek:AccWeek([SaleDate])

Usage Example-2:

Use it in a Text Box on a Form or Report:

=AccWeek([SaleDate])

Usage Example-3:

Call from within your own Code:

intSaleWeek = AccWeek(dtSaleDate)

Note: AccWeek() Function is not extensively tested in field conditions and may be used at your own risk. If you find any logical errors in the code, please share them with me too.

Technorati Tags: Microsoft