CR9 - DATE FORMULA FOR WEEK COLUMNS

[OrionStar]

I need a formula that would give me a column in my crosstab that will separate columns for each month BASED ON EACH 7 DAYS. As an example WEEK 1 = 1ST - 7TH, WEEK 2 = 8TH - 14TH ETC..ETC..

I have a crosstab that gives me totals by month..and now I need a crosstab formula that will do the above. Currently I'm using Crystals standard DATE by break by WEEK and it is using a SUNDAY-SATURDAY week calc.... I need actual 7 day calc based on above.

thax

"A man is only as happy,... as he makes up his mind to be...

 

[synapsevampire]

Create a formula such as:

datevar MyDate := {table.date};
Datevar MyMonth;
if len(totext(month(MyDate),0,"")) = 1 then
"Year: " &totext(year(MyDate),0,"")& " month: 0"& picture(totext(month(MyDate),0,""),"xx")&" week: " & totext(int((day(MyDate)/7)-.01),0,"")
else
"Year: " &totext(year(MyDate),0,"")& " month: "& picture(totext(month(MyDate),0,""),"xx")&" week: " & totext(int((day(MyDate)/7)-.01),0,"")

Should be a simpler means but I'm braindead right now.

Adjust out the text you don't need, keeping in mind how the text will sort.

-k

 

[OrionStar]

Sorry tooo advanced for me.... When I entered this formula..I have separate columns BUT they all say 2005. I need it si say Week: 1, Week: 2 Week: 3 etc..... OR.. Days:1-7, Days:8-14, ETCETC..

Sorry...M

"A man is only as happy,... as he makes up his mind to be...

 

[OrionStar]

Nevermind...IT WORKED>.. I just needed to expand the text field I couldn't see ALL the data..... thanks it worked...m

"A man is only as happy,... as he makes up his mind to be...