Show Date Ending on the group and Chart Crystal Reports

Question

I have a Crystal Reports that is pulling data by date range and grouping it by WEEK. Also the data is displayed in Chart. Everything works fine. However, I run in into one problem.

When I group by WEEK, it displays the start date in both grouping and chart. I would like to display the END Date. I wrote a formula to take care of this, however, for some reason the dates are off:

totext(cdate(dateadd("d",6,date({CallDate}))),"MM/dd/yyyy")

For example, I do a date range between 11/10/2013 - 11/23/2013. If I run this without a formula it groups like this:

11/10/2013

11/17/2013

I want to display

11/16/2013

11/23/2013

If I run this with the formula above, I get the following output:

11/20/2013

11/27/2013

I'm not even sure how they formula calculates 20 and 26. Any suggestions on how to fix this problem?

campagnolo_1 campagnolo_1 · Accepted Answer · 2013-12-16T20:43:37

This is a little tricky. CR assumes Sunday to be the first day of the week, unless specified otherwise. So, for example to get the first day of the week is Monday (number 2 in the default CR week), this formula will give you the first day of the week:

If DayOfWeek({@Today}) = 2 Then
{@Today}
Else If DayOfWeek({@Today}) = 3 Then
dateadd ("d",-1,{@Today})
Else If DayOfWeek({@Today}) = 4 Then
dateadd ("d",-2,{@Today})
Else If DayOfWeek({@Today}) = 5 Then
dateadd ("d",-3,{@Today})
Else If DayOfWeek({@Today}) = 6 Then
dateadd ("d",-4,{@Today})
Else If DayOfWeek({@Today}) = 7 Then
dateadd ("d",-5,{@Today})
Else If DayOfWeek({@Today}) = 1 Then
dateadd ("d",-6,{@Today})

To get you the last day of the week, which should be Sunday in a regular work week, this formula would give it to you:

If DayOfWeek({@Today}) = 1 Then
dateadd("d", +6, {@Today})
Else If DayOfWeek({@Today}) = 2 Then
dateadd("d", +5, {@Today})
Else If DayOfWeek({@Today}) = 3 Then
dateadd("d", +4, {@Today})
Else If DayOfWeek({@Today}) = 4 Then
dateadd ("d",+3,{@Today})
Else If DayOfWeek({@Today}) = 5 Then
dateadd ("d",+2,{@Today})
Else If DayOfWeek({@Today}) = 6 Then
dateadd ("d",+1,{@Today})
Else If DayOfWeek({@Today}) = 7 Then
{@Today}

So your days are:

Sunday (1)

Monday (2)

Tuesday (3)

Wednesday (4)

Thursday (5)

Friday (6)

Saturday(7)

So in your case, the formula to get the end date should be:

totext(cdate(dateadd("d",+6,date({CallDate}))),"MM/dd/yyyy")

Show Date Ending on the group and Chart Crystal Reports

2 Answers