How do I change the start day of the week from Sunday? 2

[BeeBear]

Hi,

Using Crystal V10

I have a cross tab of data thats grouped along the top by date (by week). The problem is, the week goes from Sunday to Saturday rather than Monday to Sunday.

I am using the date field as the column header, with the grouping level set to group "for each week" and displaying "the first date in the period" (choice of first or last date).


Can someone please tell me how I can change the first day of the week in this report to be Monday instead of Sunday?

Thanks in advance

Alison


regards

BB

*** Count your blessings not your problems******

 

[Madawc]

Create a formula field that gets Week and Year as DatePart. DatePart includes the option to set the first day of week. I think it would be

ToText(DatePart ("yyyy", {date1})) & "/" & ToText(DatePart ("ww", {date1}, 2))

Best to first display the field as a value on a test detail line. Use it in the crosstab once you are sure it is OK.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[BeeBear]

Hi,
I gave that a try, but all it gave me was the year followed by the week number.

EG: 2,002/18

I tried it on the Xtreme database Orders table for now as I cant access my work Dbase from home.

ToText(DatePart ("yyyy", {Orders.Order Date}),0) & "/"
& ToText(DatePart ("ww", {Orders.Order Date}, 2),0)

(I added the comma zero section at the end of each 'totext' to get rid of the decimals)

At the moment my main report groups by week within the crosstab, and shows the first day of the week, being the sunday (eg 28/Sept/08) when I want it to group by week, with the weeks being Mon-Sunday not Sunday-Saturday. (Display first day of week should read 29/sept/08

Cheers


regards

BB

*** Count your blessings not your problems******

 

[lbass]

Create a formula like this:

{table.date}-dayofweek({table.date},crMonday)+1

Use this as your column field in the crosstab--on change of day, since all dates within the week will be modified to the first day of the week.

-LB

 

[BeeBear]

Brilliant as always Lbass!! That does exactly what I wanted.

I've also used the same formula as the date grouping for the summary above the crosstab (the cross tab is in the rept footer).

Thanks


regards

BB

*** Count your blessings not your problems******