Grouping on Date: WEEK ENDING NOT BEGINNING

[ppark001]

Hi All,

I have Crystal 8.5

I am grouping on a date field by week. I thought I had an option for period starting or period ending.

I want to group by "End of Week", Sunday.

How can I do this?

Thanks,

Paula

 

[goranm]

Do you want to group by week where the week is set to be from monday-sunday ?

/Goran

 

[ppark001]

Yes, I want my reports to group by starting date Monday, ending Sunday.

Thanks

Paula

 

[emcevoy]

Hi

To group a date field weekly starting on Monday and going to Sunday, you can complete these steps:

1. Create a formula with the following text:

Truncate(((({date field} - Date(01,01,01))-2)/7),0)

2. Place this formula in the details section and create a group on it.

You can test if this formula is working by making an additional formula with the following text:

Dayofweek({date field})

The "DayofWeek" formula should also be placed in the Details section. It will display what day the date is on (i.e. 1 is for Sunday, 2 for Monday, etc...7 for Saturday)

Hope this helps Eileen McEvoy
Crystal Reports Consultant and Trainer

http://www.pacificridge.ca

emcevoy@pacificridge.ca

 

[ppark001]

Hi Eileen,

Your formula works great for grouping. I have one more question though. The formula creates a number such as
104,435. This particular number stands for 7/22/02, (which is a Monday). This is a number field and when I use a date conversion it gives me a crazy date. Do you know how I can convert this result so I can put the Monday date in the group header?

 

[goranm]

Hi !

Another way is to create a formula like this:

{datefield} - DayOfWeek({datefield}) + 2

and then group on this formulafield.

That will give you a monday date in your group header

(if you instead want the weeknumber in your GroupHeader create this formula:
DatePart ("ww", {datefield}, 2, 1) and group on it.)

/Goran