INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Common Formulas

How do I determine the correct accounting month based on '4-4-5' accounting periods? by rhinok
Posted: 23 May 02 (Edited 5 May 04)

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:
  1. The accounting year always starts on the first day of the month of the first month in the fiscal year
  2. The Accounting Year always ends on the last day of the month in the last month of the fiscal year
  3. [li]Each quarter in the year is comprised of Accounting Months as follows:[ul]
  4. Four weeks in the first month of the quarter
    • 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
  5. Four weeks in the second month of the quarter
  6. Five weeks in the third month of the quarter
    • 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
The next step was to identify the client-specific rules:
  1. The fiscal year begins on January 1st
  2. The fiscal year ends on December 31st
  3. The fiscal week ends on Saturday (many companies end the week on Friday)
Based on both the general rules and the client-specific rules listed above, I created the following formula*://@Operating MonthDateVar FirstDay;DateVar LastDay;
DateVar FirstSat;
StringVar YrText;
StringVar OpMonth;

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

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

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

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

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

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

*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:

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

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.

Back to Business Objects: Crystal Reports 4 Other topics FAQ Index
Back to Business Objects: Crystal Reports 4 Other topics Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close