How to get the Weekly or 7 days of the Date Range in Crystal Report

Question

How can I get the 7 days or weekly depends on date range in formula fields of Crystal Report?

Example: 
   Date Range from March 01, 2014 to March 31, 2014 

Output:
    Week 1 (March 01 to March 07) 
    Week 2 (March 08 to March 14) 
    Week 3 (March 15 to March 21) 
    Week 4 (March 22 to March 28)          
    Week 5 (March 29 to March 31)

but if you see the calender week 1 of march is only `first of march` assmuming first day of week is `sunday` in the same way 2nd week is `02 - March to 08 - March`... are you sure this with your requirement? — Siva, Apr 22 '14 at 08:34
No, it's not specific month and for example I will try to choose from March 1 to April 15 so it will automatically get the 7 days until it reached the last day of the time range as my example. — Captain16, Apr 22 '14 at 10:02

Slava Mokerov · Answer 1 · 2016-01-21T20:02:25.093

Custom function RangeWeekSplitter works within one year. Crystal Syntax.

Function  (dateTimeVar dFrom, dateTimeVar dTo)

// First day of the year
local dateTimeVar dBegCurrYear:= Date (Year(dFrom), 1, 1);

// Day of year (1 to 365 or 366 in a leap year)
local numberVar nFrom:= DatePart ("y", dFrom);
local numberVar nTo:= DatePart ("y", dTo);

local numberVar i;
local numberVar iTo;
local numberVar nDaysInWeek:= 7; // number of days in the week
local numberVar nWeek:= 0; // counter weeks
local stringVar sResult:= ""; // output string

for i:= nFrom to nTo step nDaysInWeek do
(
    nWeek:= nWeek+1;
    iTo:= i+(nDaysInWeek-1);

    if(i+nDaysInWeek > nTo)
        then iTo:= nTo;

    // generate output string
    sResult:= sResult + chr(13)+
        "Week " + CStr(nWeek) + " (" +
            CStr(DateAdd ("y", i-1, dBegCurrYear), "MMMM d") +
                " to " +
            CStr(DateAdd ("y", iTo-1, dBegCurrYear), "MMMM d") +
        ")";
);

sResult;

Usage example:

// Date range
local dateTimeVar dFrom:= Date (2016, 1, 14);
local dateTimeVar dTo:= Date (2016, 3, 4);

RangeWeekSplitter (dFrom, dTo);

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

You can use DatePart with "ww", too. See IBM Knowledge Center:

DatePart (intervalType, inputDateTime)

...

ww: Week of year (1 to 53 with firstDayOfWeek and firstWeekOfYear determining the exact days of the first calendar week of the year)

Combined with DatePart ("w", inputDateTime) or DayOfWeek(inputDateTime) getting the day of week, you can calculate your first and last day of the current calendar week.

So for one specific date (inputDateTime), this would be your formula "RangeWeek":

Function (DateTimeVar inputDateTime)
NumberVar cw := DatePart("ww", inputDateTime);
DateTimeVar first := DateAdd("d", 1 - DayOfWeek(inputDateTime, crMonday), inputDateTime);
DateTimeVar last := DateAdd("d", 7 - DayOfWeek(inputDateTime, crMonday), inputDateTime);

"Week " + ToText(cw) + " (" + ToText(first) + " to " + ToText(last) + ")"

You need to give ToText the format strings you want, of course.

Example:

Input:  "August 23, 2017"
Output: "Week 34 (August 21 to August 27)"

That makes things easier in the formula where you get the date range.

DateTimeVar from := ...;
DateTimeVar to := ...;
NumberVar cw;
NumberVar count := 0;
StringVar output := "";
for cw := DatePart("ww", from) to DatePart("ww", to) do
(
    output := output + chr(13) + RangeWeek(DateAdd("d", 7*count, from));
    count := count + 1;
);

output

How to get the Weekly or 7 days of the Date Range in Crystal Report

2 Answers2