If you group on a date field in Crystal Reports, the groups are always based on calendar periods. So a group by year will be by calendar year and a group by week will be by calendar week (Sunday to Saturday). If you want to group by a non-calendar period, like a fiscal year, then you have to write formulas. I have BLOG entries that show you how to group by fiscal year and fiscal quarter.

One of my former students asked me today how to group dates into weeks that aren’t calendar weeks, like their pay week that starts on a Monday and ends on a Sunday. I sent her a quick version, specific to her week and then decided to make a generic version that I could use with other customers. Below is a formula that you can use to have weekly groups that start on any day of the week. Your date field goes in the first line. You specify the start of your week by putting a number in the second line between 1 (Sunday) and 7 (Saturday). In the example below the number 4 means start the week on Wednesday. (You don’t change the number 7 in the last line.)

There is one important rule to remember when using this formula. When you group on the formula you should set the grouping option to be “for each DAY” rather than “for each week”. If you group “for each WEEK” the group NAME will be the SUNDAY before your week, even though the 7 dates in each group will be right. But if you remember to group “for each DAY” you will get both the correct weekly group AND the correct group name.

DateTimeVar YourDate := {Orders.Order Date};
Numbervar WeekStart :=4;
Date (YourDate) - DayOfWeek (YourDate) + WeekStart
- (if DayOfWeek (YourDate) < WeekStart then 7)