Calculating periods in 5-4-4

Monday 30 July 2007 @ 10:19 pm

I recently worked with a customer who used the 5-4-4-accounting period schedule. This means that each quarter is made up of 13 weeks, with the first month of each quarter being 5 weeks long and the other 2 being 4 weeks long. This makes for a 364 day year, which means once every 7 years or so you need to have a 53 week year. The challenge is that the year always starts either a few days before or after January 1.

This customer had some fairly complex Crystal Reports formulas that were difficult to maintain each year and so I tried to come up with a simpler process. I came up with a set of 3 formulas, only 2 of which need to be modified each year. The modification is that the beginning date of the new year needs to be added to both formulas. There are slightly simpler method that involves range variables, but I decided to go with an approach that more users could comprehend and modify to suit their needs.

The first formula takes any transaction date and determines the correct year for that transaction. It uses the starting date for each year and looks like this:

if {transaction date} >= Date (2008, 1, 6) then 2008 else
if {transaction date} >= Date (2007, 1, 7) then 2007 else
if {transaction date} >= Date (2006, 1, 8) then 2006 else
if {transaction date} >= Date (2005, 1, 9) then 2005 else
if {transaction date} >= Date (2004, 1, 4) then 2004 else 2003

The second formula reads the formula above and returns the start date of the current year, which can then be used to determine the period of the transaction date. Notice that the dates are the same in both formulas.

if {@Year} = 2008 then date(2008, 1, 6) else
if {@Year} = 2007 then date(2007, 1, 7) else
if {@Year} = 2006 then date(2006, 1, 8) else
if {@Year} = 2005 then date(2005, 1, 9) else
if {@Year} = 2004 then date(2004, 1, 4) else
if {@Year} = 2003 then date(2003, 1, 5)

The third formula doesn’t need to be maintained. It uses the second formula to determine how many weeks there are between the beginning of the year and the transaction date. This allows you to know the period for that date.

if {transaction date} – {@FY Start} >= 336 then 12 else
if {transaction date} – {@FY Start} >= 308 then 11 else
if {transaction date} – {@FY Start} >= 273 then 10 else
if {transaction date} – {@FY Start} >= 245 then 9 else
if {transaction date} – {@FY Start} >= 217 then 8 else
if {transaction date} – {@FY Start} >= 182 then 7 else
if {transaction date} – {@FY Start} >= 154 then 6 else
if {transaction date} – {@FY Start} >= 126 then 5 else
if {transaction date} – {@FY Start} >= 91 then 4 else
if {transaction date} – {@FY Start} >= 63 then 3 else
if {transaction date} – {@FY Start} >= 35 then 2 else 1

Note that some business use 4-4-5 or 4-5-4 and so you would need to adjust the numbers in this last formula.

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)







Leave a Reply

Jeff-Net
Recrystallize Pro

The Expert Series