How do I determine the correct accounting month based on '4-4-5' accounting periods?

Recently, I was tasked to build a series of reports that were based on standard '4-4-5' accounting months (referred to as 'Operating Months' by my client and, therefore, in my formula). Since native Crystal Reports Date functions are based on calendar months, I had to come up with a formula to identify the specific accounting month for each record in my report.

The first step in this process was to identify the rules by which a '4-4-5' accounting month is based. Following are the general rules:[ol][li]The accounting year always starts on the first day of the month of the first month in the fiscal year[/li][li]The Accounting Year always ends on the last day of the month in the last month of the fiscal year[/li][li]Each quarter in the year is comprised of Accounting Months as follows:[ul][li]Four weeks in the first month of the quarter[/li][ul][li]unless the first day of the fiscal year falls on the last day of the fiscal week- in this case the first fiscal month is comprised of four weeks plus 1 day[/li][/ul][li]Four weeks in the second month of the quarter[/li][li]Five weeks in the third month of the quarter[/li][ul][li]except for the last fiscal month since the last accounting month runs all the way to the end of the fiscal year, regardless of the number of weeks[/li][/ul][/ol]The next step was to identify the client-specific rules:[ol][li]The fiscal year begins on January 1st[/li][li]The fiscal year ends on December 31st[/li][li]The fiscal week ends on Saturday (many companies end the week on Friday)[/li][/ol]Based on both the general rules and the client-specific rules listed above, I created the following formula*:

[color green]//@Operating Month[/color]
[color blue]DateVar[/color] FirstDay;
[color blue]DateVar[/color] LastDay;
[color blue]DateVar[/color] FirstSat;
[color blue]StringVar[/color] YrText;
[color blue]StringVar[/color] OpMonth;

[color green]//Defines the Date Value for the First Day of the Year[/color]
FirstDay := [color blue]Date[/color]([color blue]Year[/color]({TEST_TABLE.TEST_DATE}),1,1);[color green]//Substitute your fiscal Month and Day values if different[/color]

[color green]//Defines the Date Value for the Last Day of the Year[/color]
LastDay := [color blue]Date[/color]([color blue]Year[/color]({TEST_TABLE.TEST_DATE}),12,31); [color green]//Substitute your fiscal Month and Day values if different[/color]

[color green]//Defines the Date Value for the First Saturday of the Year - modify this formula if your fiscal week ends on Friday[/color]
[color blue]If[/color]
[color blue]DayOfWeek[/color](FirstDay) < 7
[color blue]Then[/color]
FirstSat := FirstDay + (7 - [color blue]DayOfWeek[/color](FirstDay))
[color blue]Else If[/color]
[color blue]DayOfWeek[/color](FirstDay) = 7
[color blue]Then[/color]
FirstSat := FirstDay + 7;[color green]//Pushed out a week because the first day of the year was a Saturday[/color]

[color green]//Defines the YrText Variable in 'YYYY' format[/color]
YrText := [color blue]Left[/color]([color blue]ToText[/color]([color blue]Year[/color]({TEST_TABLE.TEST_DATE}),0),1) + [color blue]Right[/color]([color blue]ToText[/color]([color blue]Year[/color]({TEST_TABLE.TEST_DATE}),0),3);

[color green]//Defines the Actual Operating Month for the Test Date[/color]
[color green]//Modify the '/MM' values to your fiscal year[/color]
OpMonth := [color blue]Select[/color] {TEST_TABLE.TEST_DATE}
[color blue]Case[/color] FirstDay [color blue]to[/color] (FirstSat + 21): YrText + '/01'[color green]//January 1 to the end of the 4th Saturday[/color]
[color blue]Case[/color] (FirstSat + 22) [color blue]to[/color] (FirstSat + 49): YrText + '/02'[color green]//28 Day Range (4 weeks, Sunday to Saturday) [/color]
[color blue]Case[/color] (FirstSat + 50) [color blue]to[/color] (FirstSat + 84): YrText + '/03'[color green]//35 Day Range (5 weeks, Sunday to Saturday) [/color]
[color blue]Case[/color] (FirstSat + 85) [color blue]to[/color] (FirstSat + 112): YrText + '/04'[color green]//28 Day Range (4 weeks, Sunday to Saturday) [/color]
[color blue]Case[/color] (FirstSat + 113) [color blue]to[/color] (FirstSat + 140): YrText + '/05'[color green]//28 Day Range (4 weeks, Sunday to Saturday) [/color]
[color blue]Case[/color] (FirstSat + 141) [color blue]to[/color] (FirstSat + 175): YrText + '/06'[color green]//35 Day Range (5 weeks, Sunday to Saturday) [/color]
[color blue]Case[/color] (FirstSat + 176) [color blue]to[/color] (FirstSat + 203): YrText + '/07'[color green]//28 Day Range (4 weeks, Sunday to Saturday) [/color]
[color blue]Case[/color] (FirstSat + 204) [color blue]to[/color] (FirstSat + 231): YrText + '/08'[color green]//28 Day Range (4 weeks, Sunday to Saturday) [/color]
[color blue]Case[/color] (FirstSat + 232) [color blue]to[/color] (FirstSat + 266): YrText + '/09'[color green]//35 Day Range (5 weeks, Sunday to Saturday) [/color]
[color blue]Case[/color] (FirstSat + 267) [color blue]to[/color] (FirstSat + 294): YrText + '/10'[color green]//28 Day Range (4 weeks, Sunday to Saturday) [/color]
[color blue]Case[/color] (FirstSat + 295) [color blue]to[/color] (FirstSat + 322): YrText + '/11'[color green]//28 Day Range (4 weeks, Sunday to Saturday) [/color]
[color blue]Case[/color] (FirstSat + 323) [color blue]to[/color] LastDay: YrText + '/12'[color green]//Beginning of the Operating month to December 31st[/color];

OpMonth;[color green]//This is the return value for the Operating Month in 'YYYY/MM' format for easy sorting and grouping[/color]

*Due to the Case logic, this formula will only work in Crystal Reports 8.0 and above. In order to work in earlier versions, you will need to substitute If-Then-Else statements as follows:

[color blue]If[/color]
{TEST_TABLE.TEST_DATE} [color blue]in[/color] FirstDay [color blue]to[/color] (FirstSat + 21)
[color blue]Then[/color]
OpMonth := YrText + '/01'[color green]//January 1 to the end of the 4th Saturday[/color]
[color blue]Else If[/color][color green]//etcà[/color]

I think you'll find that the above formula works very well. For my client, the database field date (referenced as {TEST_TABLE.TEST_DATE}) '01/26/2002' would return '2002/01' as the Operating Month, but '01/27/2002' would return '2002/02'. These are the correct Operating Months, based on my client's '4-4-5' accounting periods.